UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Logic Help, Any Versions    
 
   
LilAnnCC1
post May 7 2019, 08:07 AM
Post#1



Posts: 805
Joined: 31-May 04
From: Wisconsin, USA


Hello all!

I need your help in setting up some logic (on-going debate on whether I have any, but that's another discussion!).

We have to charge sales tax under certain circumstances and I need to create some procedures based on this logic.

A part (item sold) can be tax exempt
A customer can be tax exempt
A state (while not tax exempt--does not required us to collect sales tax)

Depending on FOB we could possibly charge sales tax on customer's ship location or our dock location.

The Part table has TaxExempt (bit, not null)
The Customer table has Exempt (bit, not null) (I also have another table to store the exemption certificate information)
The StateTax table has Exempt (bit, null)
The Sales table has FOB (tinyint, null)--If this equals 2 then it needs to be our State and local taxes, otherwise it would be customer location taxes.

So in the code below, how would I check for all these factors and insert the correct tax information into the SalesInvoiceDetail table? Currently, it's grabbing the tax info from the address table of the location.

Working code with just the customer address tax info:

CODE
ALTER Procedure [dbo].[spSalesPackListInvoice]
    @intOrderID int,
    @intPackID int
    
AS

SET NOCOUNT ON;  
DECLARE @NewInvID int

--Check for existing Invoice for Pack List
IF Not EXISTS (SELECT * FROM SalesInvoice WHERE PackID=@intPackID)
BEGIN
    Begin Transaction [InvHeader]
    BEGIN TRY
--Existing Sales Order
IF EXISTS (SELECT * FROM SalesInvoice WHERE OrderID=@intOrderID)
BEGIN

--Invoice Header
    Insert Into SalesInvoice
    (
    OrderID,
    InvNo,
    Invoice,
    OrderTypeID,
    TeamID,
    SalesRepID,
    JobProjectID,
    GLID,
    InvoiceDate,
    InvoiceDue,
    CustomerID,
    CustomerPO,
    TermsID,
    Tax_Exempt,
    OBO_ID,
    StoreID,
    ShipOptionID,
    FOBID,
    Bill,
    BillTo,
    Ship,
    ShipTo,
    ShipDate,
    PackID,
    Tracking,
    ProjectID,
    ContractID,
    StatusID,
    DC,CB
)
    SELECT OrderID, 1 As NextInv,
    Concat('IN-', SUBSTRING(o.OrderNumber,4,100),'-', r.RevNumber, '-', 1) as InvNo, 3 as OrderType, o.TeamID, o.SalesRepID, o.JobProjectID, 3 As GL,
    p.ShipDate, DATEADD(day, TermDays, ShipDate) as InvDue, o.CustomerID, o.CustomerPO, o.TermsID, o.Tax_Exempt, o.OBO_ID, o.StoreID, o.ShipOptionID, o.FOBID,
    o.Bill, a.AddressBlock,o.Ship,  a1.AddressBlock, p.ShipDate as Ship, p.ID, p.Tracking, o.ProjectID, o.ContractID, 15 as Status, getdate() as DC, (substring(suser_sname(),(8),len(suser_sname()))) as CB

    FROM SalesPackList p
    
    INNER JOIN Sales o on p.OrderID=o.ID
    INNER JOIN PeopleTerms t on o.TermsID=t.ID
    INNER JOIN Revisions r on o.RevisionID=r.ID
    LEFT JOIN Address a on o.Bill=a.ID
    Left JOIN Address a1 on o.Ship=a1.ID

    WHERE OrderID=@intOrderID        
    
END
ELSE
BEGIN

--New Invoice (Invoice Header)
    Insert Into SalesInvoice
    (
    OrderID,
    InvNo,
    Invoice,
    OrderTypeID,
    TeamID,
    SalesRepID,
    JobProjectID,
    GLID,
    InvoiceDate,
    InvoiceDue,
    CustomerID,
    CustomerPO,
    TermsID,
    Tax_Exempt,
    OBO_ID,
    StoreID,
    ShipOptionID,
    FOBID,
    Bill,
    BillTo,
    Ship,
    ShipTo,
    ShipDate,
    PackID,
    Tracking,
    ProjectID,
    ContractID,
    StatusID,
    DC,CB
)
    SELECT OrderID, 1 As NextInv,
    Concat('IN-', SUBSTRING(o.OrderNumber,4,100),'-', r.RevNumber, '-', 1) as InvNo, 3 as OrderType, o.TeamID, o.SalesRepID, o.JobProjectID, 3 As GL,
    p.ShipDate, DATEADD(day, TermDays, ShipDate) as InvDue, o.CustomerID, o.CustomerPO, o.TermsID, o.Tax_Exempt, o.OBO_ID, o.StoreID, o.ShipOptionID, o.FOBID,
    o.bill,a.AddressBlock,o.ship,a1.AddressBlock, p.ShipDate as Ship, p.ID, p.Tracking, o.ProjectID, o.ContractID, 15 as Status, getdate() as DC, (substring(suser_sname(),(8),len(suser_sname()))) as CB

    FROM SalesPackList p
    
    INNER JOIN Sales o on p.OrderID=o.ID
    INNER JOIN PeopleTerms t on o.TermsID=t.ID
    INNER JOIN Revisions r on o.RevisionID=r.ID
    LEFT JOIN Address a on o.Bill=a.ID
    Left JOIN Address a1 on o.Ship=a1.ID
    WHERE OrderID=@intOrderID
        
END    


set @NewInvID=SCOPE_IDENTITY()

--Invoice Detail
    Insert Into SalesInvoiceDetail
    (
        InvoiceID, OBOID, StoreID, JobProjectID, PartID, Description, UOMID, Sold, Price, Line,
        TaxStateID, StateRate, TaxCountyID, CountyRate, TaxCityID, CityRate, TaxOtherID, OtherRate
    )

    Select @NewInvID, sd.OBOID, sd.StoreID, sd.JobProjectID, sd.PartID, sd.Description, sd.UOMID, pd.ShipQTY, sd.OrderPrice, pd.Line,
             a.TaxStateID,a.StateRate, a.TaxCountyID, a.CountyRate, a.TaxCityID, a.CityRate, a.TaxOtherID, a.OtherRate

    FROM SalesPackListDetail PD
    INNER JOIN SalesDetail sd on pd.OrderDetID=sd.ID
    LEFT JOIN vw_AddressTaxRates a on sd.ShipAddressID=a.AddressID
    WHERE pd.PackID=@IntPackID
    
    Update SalesDetail
    Set StatusID=15
    FROM SalesDetail sd
    INNER JOIN SalesPackListDetail sp on sd.id=sp.OrderDetID
    WHERE sp.PackID=@intPackID

    COMMIT TRANSACTION    

    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION
    END CATCH
    END







--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post May 7 2019, 02:35 PM
Post#2



Posts: 3,163
Joined: 27-February 09



Depending on FOB we could possibly charge sales tax on customer's ship location or our dock location.

The Part table has TaxExempt (bit, not null)
The Customer table has Exempt (bit, not null) (I also have another table to store the exemption certificate information)
The StateTax table has Exempt (bit, null)
The Sales table has FOB (tinyint, null)--If this equals 2 then it needs to be our State and local taxes, otherwise it would be customer location taxes.

I would start at the outside-most TaxExempt status.
So if the Customer has TaxExempt = 1 / True, then you can skip the rest. The TaxRate = 0.0 for the whole invoice.
If the Customer.[State] is TaxExempt, then do the same.
If neither of the above is true, then evaluate FOB.
Go to the top of the page
 
LilAnnCC1
post May 7 2019, 06:33 PM
Post#3



Posts: 805
Joined: 31-May 04
From: Wisconsin, USA


Perfect! Thank you! I knew someone could point me in the right direction!

Thank you so much!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th July 2019 - 05:48 AM