USE [001_002_003]
GO
/****** Object: View [dbo].[apa_file] Script Date: 02/26/2014 14:09:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[apa_file]
AS
SELECT
(CASE SUBSTRING(帳款編號, 1, 3) WHEN 'APA' THEN '00' WHEN 'WAP' THEN '15' WHEN 'XAP' THEN '23' WHEN 'QAP' THEN '22' WHEN 'HAP' THEN '11' WHEN 'BAP' THEN '21' WHEN 'NAP' THEN '12' ELSE '' END) AS apa00
, a.帳款編號 AS apa01
, CAST(a.帳款日期 AS date) AS apa02
, a.請款廠商 AS apa05
, a.付款廠商 AS apa06
, b.廠商簡稱 AS apa07
, 發票號碼 AS apa08
, CAST(a.帳款日期 AS date) AS apa09
, a.付款條件 AS apa11
--, CAST(a.付款日 AS date) AS apa12
,cast((case [付款日] when '1900-01-01' then null else [付款日] end) as date) as apa12
, a.幣別 AS apa13
, a.匯率 AS apa14
, a.稅別 AS apa15
,a.稅率 AS apa16
, 1 AS apa17
, 0 AS apa171
, 3 AS apa172
, NULL AS apa173
, NULL AS apa174
, NULL AS apa175
, CAST(NULL AS CHAR) AS apa18
, a.留置 AS apa19
,a.留置金額 AS apa20
, a.人員 AS apa21
, a.部門 AS apa22
, CAST(NULL AS CHAR) AS apa23
, c.允許票期 AS apa24
, a.備註 AS apa25
, a.未稅原幣 AS apa31f
, a.稅額原幣 AS apa32f
, 0 AS apa33f
, a.應付原幣 AS apa34f
, a.已付原幣 AS apa35f
, CAST(a.未稅本幣 as decimal(15,0)) AS apa31 --a.未稅本幣 AS apa31
, CAST(a.稅額本幣 as decimal(15,0)) AS apa32--a.稅額本幣 AS apa32
, 0 AS apa33
--, CAST(a.應付本幣 as decimal(15,0)) as apa34--a.應付本幣 AS apa34
,CAST(a.未稅本幣 as decimal(15,0))+CAST(a.稅額本幣 as decimal(15,0))-CAST(a.本幣折讓扣款金額 as decimal(15,0))-CAST(a.本幣折讓扣款稅額 as decimal(15,0))-CAST(a.沖帳本幣 as decimal(15,0)) as apa34
, CAST(a.已付本幣 as decimal(15,0)) AS apa35--a.已付本幣 AS apa35
, a.類別 AS apa36
, (CASE a.會財確認 WHEN '1' THEN 'Y' WHEN '0' THEN 'N' ELSE 'N' END) AS apa41
, (CASE a.結案 WHEN '1' THEN 'Y' WHEN '0' THEN 'N' END) AS apa42
, CAST(GETDATE() AS date) AS apa43
, a.傳票 AS apa44
,CAST(NULL AS DATE) AS apa45
, CAST(NULL AS CHAR) AS apa46
, a.未稅科目 AS apa51
, a.稅額科目 AS apa52
, CAST(NULL AS CHAR) AS apa53
, a.合計金額科目 AS apa54
, (case len(a.付款處理) when 0 then '1' else 付款處理 end) AS apa55--a.付款處理 AS apa55--edit by xm 20130701
, a.差異處理 AS apa56
, a.原幣單身合計金額 AS apa57f
, CAST(a.本幣單身合計金額 as decimal(15,0)) as apa57--a.本幣單身合計金額 AS apa57
, a.折讓類別 AS apa58
, CAST(NULL AS CHAR) AS apa59
, a.原幣折讓扣款金額 AS apa60f
, a.原幣折讓扣款稅額 AS apa61f
,CAST(a.本幣折讓扣款金額 as decimal(15,0)) as apa60-- a.本幣折讓扣款金額 AS apa60
,CAST(a.本幣折讓扣款稅額 as decimal(15,0)) as apa61-- a.本幣折讓扣款稅額 AS apa61
, CAST(NULL AS CHAR) AS apa62
, 2 AS apa63
--, CAST(a.到期日 AS date) AS apa64
,cast((case [到期日] when '1900-01-01' then null else [到期日] end) as date) as apa64
, a.沖帳原幣 AS apa65f
, CAST(a.沖帳本幣 as decimal(15,0)) as apa65--a.沖帳本幣 AS apa65
, CAST(NULL AS CHAR) AS apa66
, CAST(NULL AS CHAR) AS apa67
, 'CPPI' AS apa68
, CAST(NULL AS CHAR) AS apa69
, CAST(NULL AS CHAR) AS apa70
, CAST(NULL AS CHAR) AS apa71
, 'N' AS apa72
,a.週月結 AS apa73
, (CASE a.確認狀態 WHEN '1' THEN 'Y' WHEN '0' THEN 'N' ELSE 'N' END) AS apa74
, (CASE a.拋轉狀態 WHEN '1' THEN 'Y' WHEN '0' THEN 'N' ELSE 'N' END) AS apa75
, CAST(GETDATE() AS date) AS apainpd
, CAST(NULL AS CHAR) AS apamksg
, CAST(NULL AS CHAR) AS apasign
, NULL AS apadays
, NULL AS apaprit
, NULL AS apasmax
, NULL AS apasseq
, 0 AS apaprno
,(CASE a.確認狀態 WHEN '1' THEN 'Y' WHEN '0' THEN 'N' ELSE 'N' END) AS apaacti
, a.人員 AS apauser
, a.部門 AS apagrup
, a.人員 AS apamodu
, CAST(GETDATE() AS date) AS apadate
, CAST(NULL AS CHAR) AS apa251
FROM dbo.應付帳款單頭檔 AS a LEFT OUTER JOIN dbo.供應商基本資料檔 AS b ON a.付款廠商 = b.廠商代碼
left outer join dbo.付款方式檔 c on a.付款條件=c.付款方式
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[24] 4[11] 2[62] 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 = -672
Left = 0
End
Begin Tables =
Begin Table = "a"
Begin Extent =
Top = 6
Left = 38
Bottom = 125
Right = 223
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "b"
Begin Extent =
Top = 6
Left = 261
Bottom = 125
Right = 421
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 89
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
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'apa_file'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' 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
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'apa_file'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'apa_file'
GO
範例:
use YY1
SELECT a.[國家碼]
,a.[幣別]
,b.[國家名稱]
,b.[國籍]
,c.表C國家碼
,c.表C幣別
FROM [YY1].[dbo].[國別表$] as a
LEFT JOIN
[YY1].[dbo].[國家名稱$] as b on a.國家碼= b.[國家碼]
LEFT JOIN
tempYY001 as c on a.國家碼= c.表C國家碼