-
Notifications
You must be signed in to change notification settings - Fork 0
/
vendor.sql
115 lines (115 loc) · 3.35 KB
/
vendor.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
/****** Script for SelectTopNRows command from SSMS ******/
SELECT t1.[accountnum],
t3.NAME,
t4.enumitemname,
t1.[transdate],
t1.[voucher],
t1.[invoice],
t1.[txt],
t1.[amountcur],
t1.[settleamountcur],
t1.[amountmst],
t1.[settleamountmst],
t1.[currencycode],
t1.[duedate],
t1.[lastsettlevoucher],
t1.[lastsettledate],
t1.[closed],
t1.[transtype],
t1.[approved],
t1.[paymid],
[exchadjustment],
[documentnum],
[documentdate],
[arrival],
[lastexchadj],
[correct],
[lastexchadjvoucher],
[lastexchadjrate],
[postingprofile],
[settlement],
[cancel],
[postingprofileclose],
[postingprofileapprove],
[postingprofilecancel],
[postingprofilereopen],
[thirdpartybankaccountid],
[companybankaccountid],
[paymreference],
t1.[paymmode],
[tax1099date],
[tax1099amount],
[tax1099num],
[offsetrecid],
[journalnum],
[eurotriangulation],
[cashdisccode],
[prepayment],
t1.[paymspec],
[vendexchadjustmentrealized],
[vendexchadjustmentunrealized],
[approveddate],
[promissorynoteid],
[promissorynotestatus],
[promissorynoteseqnum],
[bankremittancefileid],
[fixedexchrate],
t1.[bankcentralbankpurposetext],
t1.[bankcentralbankpurposecode],
[tax1099state],
[tax1099stateamount],
[settletax1099amount],
[settletax1099stateamount],
t1.[defaultdimension],
[exchratesecond],
[exchrate],
[lastsettleaccountnum],
[lastsettlecompany],
[invoiceproject],
[reasonrefrecid],
[releasedatecomment],
[invoicereleasedate],
[invoicereleasedatetzid],
[vendpaymentgroup],
[remittancelocation],
[remittanceaddress],
t1.[tax1099fields],
[banklcimportline],
[accountingevent],
[approver],
[reportingcurrencyamount],
[reportingexchadjustmentrealized],
[reportingexchadjustmentunrealized],
[lastexchadjratereporting],
[reportingcurrencycrossrate],
[exchadjustmentreporting],
[settleamountreporting],
[taxinvoicepurchid],
[tax1099recid],
[consessionsettlementid],
[rbovendtrans],
t1.[modifieddatetime],
t1.[del_modifiedtime],
t1.[modifiedby],
t1.[modifiedtransactionid],
t1.[createddatetime],
t1.[del_createdtime],
t1.[createdby],
[createdtransactionid],
t1.[dataareaid],
t1.[recversion],
t1.[partition],
t1.[recid]
FROM [MITAX_live].[dbo].[vendtrans] AS t1
LEFT JOIN vendtable AS t2
ON t1.accountnum = t2.accountnum
AND t1.dataareaid = t2.dataareaid
LEFT JOIN dirpartytable AS t3
ON t2.party = t3.recid
LEFT JOIN srsanalysisenums AS t4
ON t1.transtype = t4.enumitemvalue
AND t4.enumname = 'LedgerTransType'
AND t4.languageid = 'en-us'
WHERE t1.dataareaid = 'myla'
AND t2.vendgroup IN ( 'Domes', 'Purchase' )
AND NAME = 'name of vendor'