跪求 APTB和ARTB 视图或代码

songhua_Leon

APTB和ARTB怎么计算帐龄？最好有视图或代码

该贴已经同步到 songhua_Leon的微博

azefield

做一个吧，

songhua_Leon

azefield 发表于 2011/11/3 15:24
做一个吧，

请教怎样做？

chinzys

songhua_Leon 发表于 2011/11/7 16:00
请教怎样做？

ARTB 请参考：  http://luntan.ERP100.com/thread-55932-1-1.html

songhua_Leon

我自己写了一个APTB
Select ap.VendorID, v1.VendorName, ap.InvoiceNumber,ap.InvoiceDate,ap.SurplusInvoiceAmount
into #Rep_APTB
From
        (Select h.VendorID,h.InvoiceNumber,h.InvoiceDate,
         Case When hd.InvoiceControllingCode ='L'
                  Then Round(hd.InvoiceLocalAmount - hd.DiscountTakenLocalAmount - hd.PaymentLocalAmount,2)
                  When hd.InvoiceControllingCode ='F'
                  Then Round(hd.InvoiceForeignAmount - hd.DiscountTakenForeignAmount - hd.PaymentForeignAmount,2) End As SurplusInvoiceAmount
         From FS_APInvoiceHeader h Inner Join FS_APInvoiceHeaderData hd
         On h.APInvoiceHeaderKey = hd.APInvoiceHeaderKey
Union All
         Select v.VendorID,'' as InvoiceNumber,'' as InvoiceDate,  -vd.UnappliedPaymentLocalAmount as SurplusInvoiceAmount  
         From FS_Vendor v Inner Join FS_VendorData vd On v.VendorKey = vd.VendorKey ) ap
Inner Join FS_Vendor v1 On ap.VendorID = v1.VendorID
Where ap.SurplusInvoiceAmount <>0
Order By ap.VendorID

Select a.* From
(   Select VendorID, VendorName,InvoiceNumber,InvoiceDate,
        Case When DateDiff(Day,InvoiceDate,Getdate())>0 And DateDiff(Day,InvoiceDate,Getdate())<=30
                 Then SurplusInvoiceAmount END As A1_30,
        Case When DateDiff(Day,InvoiceDate,Getdate())>30 And DateDiff(Day,InvoiceDate,Getdate())<=60
                 Then SurplusInvoiceAmount END As A31_60,
        Case When DateDiff(Day,InvoiceDate,Getdate())>60 And DateDiff(Day,InvoiceDate,Getdate())<=90
                 Then SurplusInvoiceAmount END As A61_90,
        Case When DateDiff(Day,InvoiceDate,Getdate())>90 And DateDiff(Day,InvoiceDate,Getdate())<=180
                 Then SurplusInvoiceAmount END As A90_180,
        Case When DateDiff(Day,InvoiceDate,Getdate())>180 Then SurplusInvoiceAmount END As A180,'' as NoAmount
        From #Rep_APTB Where InvoiceDate<>'1900-01-01'  
Union All
        Select VendorID, VendorName,InvoiceNumber,InvoiceDate,
        '' as A1_30,'' as A31_60,'' as A61_90,'' as A90_180, '' as A180, SurplusInvoiceAmount as NoAmount
        From #Rep_APTB Where InvoiceDate='1900-01-01' ) a
Order By VendorID,InvoiceNumber Desc,InvoiceDate

songhua_Leon

chinzys 发表于 2011/11/7 23:03
ARTB 请参考：  http://luntan.ERP100.com/thread-55932-1-1.html

学习，谢谢

songhua_Leon

ARTB 也写出来了
Select a.CustomerID,a.InvoiceNumber,a.InvoiceDate,
Round(Sum(IsNull(a.TotalInvoiceForeignAmount,0) - Isnull(b.CashReceiptAppliedLocalAmount,0)),2) as TotalInvoiceAmount
into #Rep_ARInvoiceAmount
From
        (Select CustomerID,InvoiceNumber,InvoiceDate,
         Sum(Case When InvoiceType ='R' Then TotalInvoiceForeignAmount*-1
                          Else TotalInvoiceForeignAmount End) as TotalInvoiceForeignAmount
         From FS_ARInvoiceHeader Where InvoiceStatus <>'U'
         Group By CustomerID,InvoiceNumber,InvoiceDate ) a
Left Join
         (Select h.CustomerID,p.InvoiceNumber,h.InvoiceDate,
          Sum(Case When InvoiceType ='R' Then CashReceiptAppliedLocalAmount*-1
                           Else CashReceiptAppliedLocalAmount End) as CashReceiptAppliedLocalAmount
          From FS_ARInvoiceHeader h Inner Join FS_ARInvoicePayment p On h.ARInvoiceHeaderKey = p.ARInvoiceHeaderKey
          Where h.InvoiceStatus <>'U'
          Group By h.CustomerID,p.InvoiceNumber,h.InvoiceDate ) b
On a.CustomerID = b.CustomerID And a.InvoiceNumber = b.InvoiceNumber
Group By a.CustomerID,a.InvoiceNumber,a.InvoiceDate
Having Round(Sum(IsNull(a.TotalInvoiceForeignAmount,0) - Isnull(b.CashReceiptAppliedLocalAmount,0)),2)<>0
Order By a.CustomerID

Select * From
(        Select r.CustomerID, c.CustomerName,c.CreditLimitControllingAmount,r.InvoiceNumber,g.TRANS_DESC,
        r.InvoiceDate,'' as UnappliedCashBalanceForeignAmount,
        Case When DateDiff(Day,r.InvoiceDate,Getdate())>0 And DateDiff(Day,r.InvoiceDate,Getdate())<=30
                 Then r.TotalInvoiceAmount END As A1_30,
        Case When DateDiff(Day,r.InvoiceDate,Getdate())>30 And DateDiff(Day,r.InvoiceDate,Getdate())<=60
                 Then r.TotalInvoiceAmount END As A31_60,
        Case When DateDiff(Day,r.InvoiceDate,Getdate())>60 And DateDiff(Day,r.InvoiceDate,Getdate())<=90
                 Then r.TotalInvoiceAmount END As A61_90,
        Case When DateDiff(Day,r.InvoiceDate,Getdate())>90 And DateDiff(Day,r.InvoiceDate,Getdate())<=180
                 Then r.TotalInvoiceAmount END As A91_180,
        Case When DateDiff(Day,r.InvoiceDate,Getdate())>180 Then r.TotalInvoiceAmount END As A180
        From #Rep_ARInvoiceAmount r Inner Join FS_Customer c On r.CustomerID = c.CustomerID
        Left Join (Select Distinct REF_NO4,BATCH_NO,TRANS_DESC From GL_BATCHDETAILACCT Where BATCH_SRCE in ('INVA','IVIE','IVPR')) g
        On r.InvoiceNumber = g.REF_NO4
Union All
        Select c.CustomerID,c.CustomerName,c.CreditLimitControllingAmount,'' as InvoiceDate, '' as TRANS_DESC,
        '' as InvoiceDate, cd.UnappliedCashBalanceForeignAmount,
    '' as A1_30,'' as A31_60,'' as A61_90, '' as A91_180, '' as A180
        From FS_Customer c Inner Join FS_CustomerData cd On c.CustomerKey = cd.CustomerKey
        Where c.CustomerStatus <>'I' And c.CustomerID <>'000000' ) a
Order By a.CustomerID,a.InvoiceNumber Desc