A question came up recently on how to relate taxes in tax history (TX30000) to the service performed (SV000815)
To understand how the tables fit together, we must first understand how the records get into those tables and what they represent. (the below is not an exhaustive list of tables, just the ones we are interested in for this question)
SV00500 holds the contract information
SV00400 holds the equipment information
SV00403 holds the information on equipment assigned to contracts
SV00560 holds the main task information (such as cost and billable amounts)
SV00582 holds the information on tasks assigned to a contract and also holds information on cost and pricing
When a service call is created, there are typically 3 tables that hold the data.
SV00300 holds the main information about the call.
SV00301 holds the information on the appointments assigned to the call.
SV00302 holds the information on the tasks assigned to the call.
When a service invoice is created, information from the 8 tables above is pulled together to create records in the following:
SV00700 is the service invoice header. The table holds up to 4 tax amounts for the invoice.
SV000810 is the service call cost information. The table also holds the task code and billable amount is applicable.
RM10301 is the Receivables Header.
RM10101 is the Receivables distributions.
RM10601 is the Receivables taxes.
When the RM tables above are populated, the TX00102 and TX00201 tables are read based on the billable customer's tax schedule and the RM10601 is populated. Whatever tax is calculated there is then added the the RM10101 distributions and then summed up and added to SV00700.
When the service invoice is posted, the following records are moved:
SV000810 to SV000815
RM10301 to RM20101
SV00700 to SV00701
RM10601 is copied to TX30000, but RM10601 records remain
When the receivable is moved to history, the following records are moved:
RM10101 to RM30301
RM10601 to RM30601
RM20101 to RM30101
TX30000 is removed.
So depending on what point in the workflow the transaction is in, there might not be a record in TX30000 or in SV000815.
If the TX30000 record is related to a service invoice, the SV00701 Tax_Amount1 thru Tax_Amount4 fields should hold the values from the first 4 lines if TX30000.TAXAMNT for the invoice.
The tables can be joined in a one to many relationship as follows:
SV00701 >> TX30000
on RMDNUMWK=DOCNUMBR
and RMDTYPAL=DOCTYPE
and 3=SERIES
SV00701 >> SV000815
on Call_Invoice_Number=Call_Invoice_Number
and CUSTNMBR=CUSTNMBR
and ADRSCODE=ADRSCODE
(Note that I did not include Service_Call_ID. This is because on grouped invoices, the SV00701 Service_Call_ID is be 'GROUPED' while the SV000815 will show the actual call ids)
So if you need to get from TX30000 to SV000815 you have to go through SV00701
TX30000 << SV00701 >> SV000815
