IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vwRpAcCSRpRBMaster]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [dbo].[vwRpAcCSRpRBMaster]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---2006/08/24--- Add IsNull
---2006/09/12--- Add TotalInWord
--- 2006/10/12 --- Add TotalParts, TotalServices, DetailDiscount
--- 2007/01/25 --- Add AcCard.PointBal1, AcCard.PointBal2
--- 2007/01/31 --- Add AcCard.Handphone as CardHandphone, DefaultNotes
--- 13/09/2007 --- Cheah, Add GST
--- 03/10/2007 --- Remove AcCurrency.Code As CurrencyCode, Add fAmountWithGST 6.51030
--- 2008/01/23 --- Add StockItemList 6.51032
--- 2008/03/26 --- Add RoundAmt in NetAmt 6.51033[1]
--- 21/07/2008,6.51034[2], Keric:add field TotalAmt,RoundTotalAmt
---				:add BankCharges,Tax1 into (TotalAmt,RoundTotalAmt,TotalInWord,RoundTotalInWord)  
--- 28/10/2008,6.51035     Keric:rename Tax1 to SevCharge
--- 06/01/2008,6.51035[1]  Keric: add bankchargestype condition
--- 16/04/2010,6.60005		 Kenny: add CashierName
--- 15/10/2010,6.60008       Keric: add fGSTAmt,fGSTAdj,fGST
--- 19/07/2011,2011.2CC      Keric: add MechanicName
--- 13/03/2014,2014GST       Keric: added AcCard.GSTCode
--- 09/07/2014,2014GST       Keric: added Tax1fGSTAmt
--- 20/08/2014,2014.2     Keric:added PostCode
--- 24/10/2014,2014.2CC     Keric:added ReportTitle,RMCDAppNo_Label,RMCDAppNo

CREATE VIEW [dbo].[vwRpAcCSRpRBMaster]
AS
  (SELECT dbo.pfGetCustCSStkId(m.DocumentNo,m.CardID) AS StockItemList,
          m.*,
          CASE 
            WHEN m.BankChargesType <> 'Own'
            THEN (IsNull(m.Amount,0)
                    - IsNull(m.Discount,0)
                    + IsNull(m.Tax,0)
                    + IsNull(m.Tax1,0)
                    + IsNull(m.BankCharges,0)
                    + IsNull(m.fGSTAmt,0) 
                    + IsNull(m.fGSTAdj,0)
                    + case when (m.IsTax1CompoundTax='Y') 
                           then 0
                           else IsNull(m.Tax1fGSTAmt,0) 
                      end)
            ELSE (IsNull(m.Amount,0)
                    - IsNull(m.Discount,0)
                    + IsNull(m.Tax,0)
                    + IsNull(m.Tax1,0)
                    + IsNull(m.fGSTAmt,0) 
                    + IsNull(m.fGSTAdj,0)
                    + case when (m.IsTax1CompoundTax='Y') 
                           then 0
                           else IsNull(m.Tax1fGSTAmt,0) 
                      end)
          END AS NetAmt,
          CASE 
            WHEN m.BankChargesType <> 'Own'
            THEN (IsNull(m.Amount,0)
                    - IsNull(m.Discount,0)
                    + IsNull(m.Tax,0)
                    + IsNull(m.RoundAmt,0)
                    + IsNull(m.Tax1,0)
                    + IsNull(m.BankCharges,0)
                    + IsNull(m.fGSTAmt,0) 
                    + IsNull(m.fGSTAdj,0)
                    + case when (m.IsTax1CompoundTax='Y') 
                           then 0
                           else IsNull(m.Tax1fGSTAmt,0) 
                      end)
            ELSE (IsNull(m.Amount,0)
                    - IsNull(m.Discount,0)
                    + IsNull(m.Tax,0)
                    + IsNull(m.RoundAmt,0)
                    + IsNull(m.Tax1,0)
                    + IsNull(m.fGSTAmt,0) 
                    + IsNull(m.fGSTAdj,0)
                    + case when (m.IsTax1CompoundTax='Y') 
                           then 0
                           else IsNull(m.Tax1fGSTAmt,0) 
                      end)
          END AS RoundNetAmt,
          (IsNull(m.Amount,0)
             - IsNull(m.Discount,0)
             + IsNull(m.Tax,0)
             + IsNUll(m.Tax1,0)
             + IsNull(m.BankCharges,0)
             + IsNull(m.fGSTAmt,0) 
             + IsNull(m.fGSTAdj,0)
             + case when (m.IsTax1CompoundTax='Y') 
                    then 0
                    else IsNull(m.Tax1fGSTAmt,0) 
               end) AS TotalAmt,
          (IsNull(m.Amount,0)
             - IsNull(m.Discount,0)
             + IsNull(m.Tax,0)
             + IsNUll(m.Tax1,0)
             + IsNull(m.RoundAmt,0)
             + IsNull(m.BankCharges,0)
             + IsNull(m.fGSTAmt,0) 
             + IsNull(m.fGSTAdj,0)
             + case when (m.IsTax1CompoundTax='Y') 
                    then 0
                    else IsNull(m.Tax1fGSTAmt,0) 
               end) AS RoundTotalAmt,
          (IsNull(Amount,0)
             + IsNull(fGSTAmt,0)+IsNull(fGSTAdj,0)+IsNull(m.Tax1fGSTAmt,0)) AS fAmountWithGST,
          (IsNull(m.fGSTAmt,0)+IsNull(m.fGSTAdj,0)+IsNull(m.Tax1fGSTAmt,0)) as fGST,
          AcPrd.Period AS Prd,
          c.Tel1,
          c.Tel2,
          c.Fax,
          c.Address1,
          c.Address2,
          c.Address3,
          c.Address4,
          c.Contact1,
          c.Contact2,
          c.Name1,
          c.Notes,
          c.PointBal1,
          c.PointBal2,
          AcVM.DESCRIPTION AS VehicleModelDescription,
          c.TYPE,
          c.ROCNo,
          c.ShipID,
          c.DateReg,
          c.ChasisNo,
          c.EngineNo,
          c.DrivingExpiry,
          c.RoadExpiry,
          c.CustomerTermID,
          c.CustomerCreditLimit,
          c.CustomerBalance,
          c.CustomerOpenBalance,
          c.cfg,
          c.cardGroup,
          c.Handphone AS CardHandphone,
          c.Name AS CustName,
          c.GstCode,
          c.PostCode,
          AcSm.Name AS SmName,
          AcStkCt.DESCRIPTION AS CounterDescription,
          AcArea.DESCRIPTION AS AreaDesc,
          AcJob.DESCRIPTION AS JobDescription,
          AcCurrency.Symbol,
          acreprint.PrintBy,
          acreprint.LastPrintBy,
          acreprint.LastPrintDate,
          (CASE 
             WHEN (SELECT count(* )
                   FROM   acreprint
                   WHERE  acreprint.documentno = m.documentno
                          AND acreprint.documenttype = 'CSH') = 1
             THEN 'REPRINT'
           END) AS PrintStatus,
          AcCurrency.DESCRIPTION AS CurrencyDescription,
          AcCurrency.Units,
          (IsNull(Amount,0)
             - IsNull(Discount,0)
             + IsNull(fGSTAmt,0)
             + IsNull(fGSTAdj,0)) AS AmtAfterDisc,
          (SELECT sum(uomqty)
           FROM   accsd
           WHERE  accsd.documentno = m.documentno) AS TotaluomQty,
          ISNULL(AccDesc1,'')
            + ISNULL(CASE 
                       WHEN AccDesc1 IS NULL
                       THEN AccDesc2
                       ELSE '/'
                              + AccDesc2
                     END,'')
            + ISNULL(CASE 
                       WHEN AccDesc1 IS NULL 
                            AND AccDesc2 IS NULL
                       THEN AccDesc3
                       ELSE '/'
                              + AccDesc3
                     END,'') AS AccDesc,
          (SELECT acacc.DESCRIPTION
           FROM   acacc
           WHERE  acacc.code = m.ControlAccount
                  AND acacc.Department = m.ControlDepartment) AS PayDesc1,
          (SELECT acacc.DESCRIPTION
           FROM   acacc
           WHERE  acacc.code = m.CardAccount
                  AND acacc.Department = m.CardDepartment) AS PayDesc2,
          (SELECT acacc.DESCRIPTION
           FROM   acacc
           WHERE  acacc.code = m.OtherAccount
                  AND acacc.Department = m.OtherDepartment) AS PayDesc3,
          CASE 
            WHEN m.BankChargesType <> 'Own'
            THEN dbo.pfCurrencyToWord(Round(IsNull(m.Amount,0)
                                              - IsNull(m.Discount,0)
                                              + IsNull(m.Tax,0)
                                              + IsNull(m.Tax1,0)
                                              + IsNull(m.BankCharges,0)
                                              + IsNull(fGSTAmt,0)
                                              + IsNull(fGSTAdj,0)
                                              + case when (m.IsTax1CompoundTax='Y') 
                                                     then 0
                                                     else IsNull(m.Tax1fGSTAmt,0) 
                                                 end,2))
            ELSE dbo.pfCurrencyToWord(Round(IsNull(m.Amount,0)
                                              - IsNull(m.Discount,0)
                                              + IsNull(m.Tax,0)
                                              + IsNull(m.Tax1,0)
                                              + IsNull(fGSTAmt,0)
                                              + IsNull(fGSTAdj,0)
                                              + case when (m.IsTax1CompoundTax='Y') 
                                                     then 0
                                                     else IsNull(m.Tax1fGSTAmt,0) 
                                                 end,2))
          END AS TotalInWord,
          CASE 
            WHEN m.BankChargesType <> 'Own'
            THEN dbo.pfCurrencyToWord(Round(IsNull(m.Amount,0)
                                              - IsNull(m.Discount,0)
                                              + IsNull(m.Tax,0)
                                              + IsNull(m.Tax1,0)
                                              + IsNull(m.RoundAmt,0)
                                              + IsNull(m.BankCharges,0)
                                              + IsNull(fGSTAmt,0)
                                              + IsNull(fGSTAdj,0)
                                              + case when (m.IsTax1CompoundTax='Y') 
                                                     then 0
                                                     else IsNull(m.Tax1fGSTAmt,0) 
                                                 end,2))
            ELSE dbo.pfCurrencyToWord(Round(IsNull(m.Amount,0)
                                              - IsNull(m.Discount,0)
                                              + IsNull(m.Tax,0)
                                              + IsNull(m.Tax1,0)
                                              + IsNull(m.RoundAmt,0)
                                              + IsNull(fGSTAmt,0)
                                              + IsNull(fGSTAdj,0)
                                              + case when (m.IsTax1CompoundTax='Y') 
                                                     then 0
                                                     else IsNull(m.Tax1fGSTAmt,0) 
                                                 end,2))
          END AS RoundTotalInWord,
          (SELECT sum(IsNull(TotalParts,0))
           FROM   vwRpAcCSRpRbDetail d
           WHERE  d.documentno = m.documentno) AS TotalParts,
          
          (SELECT Cast(b.DESCRIPTION AS VARCHAR(100))
           FROM   AcServiceM b
           WHERE  b.IsDefault = 'Y') AS DefaultNotes,
          
          (SELECT sum(IsNull(TotalServices,0))
           FROM   vwRpAcCSRpRbDetail d
           WHERE  d.documentno = m.documentno) AS TotalServices,
          
          (SELECT sum(IsNull((d.UomQty
                                * d.Price),0))
           FROM   vwRpAcCSRpRbDetail d
           WHERE  d.documentno = m.documentno) AS DetailItemTotal,
          
          (SELECT sum(IsNull(Discount,0))
           FROM   vwRpAcCSRpRbDetail d
           WHERE  d.documentno = m.documentno) AS DetailDiscount,
          
          case when m.IsTax1CompoundTax='Y' 
               then (m.Tax1-m.Tax1fGSTAmt)
               else m.Tax1
          End AS SevCharge,
          p.Name As CashierName,
          AcMechanic.Name AS MechanicName,
          
          Case     
           when (m.sAllowGST='Y' and m.IsGSTReg='Y') then 'TAX INVOICE'
           else 'CASH SALES' 
          End as ReportTitle
          
   FROM   AcCSM m
          LEFT JOIN AcStkCt
            ON m.COUNTER = AcStkCt.Code
          LEFT OUTER JOIN AcPrd
            ON m.TrxDAte = AcPrd.DateStart
          LEFT OUTER JOIN AcCard c
            ON m.CardID = c.Code
          LEFT OUTER JOIN AcSm
            ON m.SalesPerson = AcSm.Code
          LEFT JOIN AcPersonal p
          	ON m.Cashier = p.Code
          LEFT OUTER JOIN AcArea
            ON m.Area = AcArea.Code
          LEFT OUTER JOIN AcJob
            ON m.JobNo = AcJOB.Code
          LEFT OUTER JOIN AcServiceM
            ON m.ServiceID = AcServiceM.Code
          LEFT OUTER JOIN AcCurrency
            ON m.Currency = AcCurrency.Code
          LEFT OUTER JOIN vwRpCSAccDesc
            ON m.DocumentNo = vwRpCSAccDesc.DocumentNo
          LEFT OUTER JOIN AcReprint
            ON (acreprint.documentno = m.documentno
                AND acreprint.documenttype = 'CSH')
          LEFT OUTER JOIN AcVM
            ON c.Name1 = AcVM.Code
          LEFT OUTER JOIN AcMechanic
            ON m.Mechanic = AcMechanic.code)
GO
