Hi,
I need some guidance on how to void old SOP orders. These should be simple enough, except they have a deposit on them. As such, it won’t let me void them. I have tried to go in and remove the cash deposit transaction, but it won’t let me do it for a closed period. These are from a few years ago, so it I am unable to open the period to remove the cash deposit.
Any suggestions on how I can clean this up?
Deborah
*This post is locked for comments
Deborah,
I would be careful using this method with out verifying there aren't corresponding records for the transaction in other tables.
I got this script to check for related entries the last time I had support help me clear some up.
/*********************************************************************************
** All SOP info for specific sales document. Includes all SOP tables as well **
** as INV, RM and GL including:
** SOP10100 - Sales Transaction Work **
** SOP10101 - Sales Commission Work and History **
** SOP10102 - Sales Distribution Work and History **
** SOP10103 - Sales Payment Work and History **
** SOP10104 - Sales Process Holds Work and History **
** SOP10105 - Sales Tax Work and Hisory **
** SOP00106 - Sales User-Defined Work History **
** SOP00107 - Sales Tracking Numbers Work History **
** SOP10200 - Sales Transaction Amounts Work **
** SOP10201 - Sales Serial/Lot Work and History **
** SOP10202 - Sales Line Comment Work and History **
** SOP10203 - Sales order Bin Quantities Work and History **
** SOP10204 - Extended Pricing Promotion Work **
** SOP30200 - Sales Transaction History **
** SOP30201 - Sales Deposit History **
** TX30000 - Tax History **
** SOP60100 - SOP_POP Link **
** IV10201 - Inventory Purchase Receipts Detail **
** IV30300 - Inventory Transaction Amounts History **
** IV30301 - Inventory Transaction Detail History **
** IV30302 - Inventory Transaction Bin Quantities History **
** shouldn't be any records in IV30400 **
** IV30500 - Inventory Distribution History **
** SEE30303 - Inventory Transaction History Detail **
** PP000100 - Deferral Header Work **
** PP000101 - Deferral Line Work **
** PP100100 - Deferral Header History **
** PP100101 - Deferral Line History **
** RM00401 - RM Key File **
** RM20101/RM30101 - RM Open / RM History **
** RM10101/RM30301 - RM Dist Open / RM Dist History **
** RM20201/RM30201 - RM Apply Open/ RM Apply History **
** GL10001/GL20000/GL30000 - GL work / GL open / GL history **
** CM20100 - CM Journal **
** CM20300 - CM Receipt **
** SVC00600 - Contract Header **
** **
** Input SOPTYPE and SOPNUMBE **
** **
** 1 Quote **
** 2 Order **
** 3 Invoice **
** 4 Return **
** 5 Back Order **
** 6 Fulfillment Order **
** Note: can tune script for in (5,6) and (1,8) statements **
*********************************************************************************/
declare @SOPTYPE smallint
declare @SOPNUMBE char(21)
select @SOPTYPE = '4'
select @SOPNUMBE = 'rma004000'
print 'Sales'
print '=================================================================================='
print ''
if exists (select * from SOP10100 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10100 - Sales Work'
select * from SOP10100 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10101 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10101 - Sales Commissions Work and History'
select * from SOP10101 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10102 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10102 - Sales Distribution Work and History'
select * from SOP10102 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10103 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10103 - Sales Payment Work and History'
select * from SOP10103 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10104 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10104 - Sales Process Holds Work and History'
select * from SOP10104 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10105 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10105 - Sales Taxes Work and History'
select * from SOP10105 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10106 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10106 - Sales User-Defined Work and History'
select * from SOP10106 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10107 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10107 - Sales Tracking Numbers Work and History'
select * from SOP10107 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10200 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10200 - Sales Transaction Amounts Work'
select * from SOP10200 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10201 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10201 - Sales Serial/Lot Work and History'
select * from SOP10201 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10202 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10202 - Sales Line Comment Work and History'
select * from SOP10202 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10203 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10203 - Sales Order Bin Quantities Work and History'
select * from SOP10203 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP10204 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP10204 - Extended Pricing Promotion Work'
select * from SOP10204 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP30200 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP30200 - Sales Transaction History'
select * from SOP30200 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP30201 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP30201 - Sales Deposit History'
select * from SOP30201 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from SOP30300 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP30300 - Sales Transaction Amounts History'
select * from SOP30300 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
if exists (select * from TX30000 where DOCNUMBR = @SOPNUMBE and SERIES = 1)
Begin
print 'TX30000 - Tax History'
select * from TX30000 where DOCNUMBR = @SOPNUMBE and SERIES = 1
End
if exists (select * from SOP60100 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE)
Begin
print 'SOP60100 - SOP_POPLink'
select * from SOP60100 where SOPTYPE = @SOPTYPE and SOPNUMBE = @SOPNUMBE
End
print 'Inventory'
print '=================================================================================='
print ''
if exists (select * from IV10201 where ORIGINDOCID = @SOPNUMBE)
Begin
print 'IV10201 - Inventory Purchase Receipts Detail'
select * from IV10201 where ORIGINDOCID = @SOPNUMBE
End
if exists (select * from IV30300 where DOCNUMBR = @SOPNUMBE and DOCTYPE in (5,6))
Begin
print 'IV30300 - Inventory Transaction Amounts History'
if @SOPTYPE = 3 (select * from IV30300 where DOCNUMBR = @SOPNUMBE and DOCTYPE = 6)
if @SOPTYPE = 4 (select * from IV30300 where DOCNUMBR = @SOPNUMBE and DOCTYPE = 5)
End
if exists (select * from IV30301 where DOCNUMBR = @SOPNUMBE and DOCTYPE in (5,6))
Begin
print 'IV30301 - Inventory Transaction Detail History'
if @SOPTYPE = 3 (select * from IV30301 where DOCNUMBR = @SOPNUMBE and DOCTYPE = 6)
if @SOPTYPE = 4 (select * from IV30301 where DOCNUMBR = @SOPNUMBE and DOCTYPE = 5)
End
if exists (select * from IV30302 where DOCNUMBR = @SOPNUMBE and DOCTYPE in (5,6))
Begin
print 'IV30302 - Inventory Transaction Bin Quantities History'
if @SOPTYPE = 3 (select * from IV30302 where DOCNUMBR = @SOPNUMBE and DOCTYPE = 6)
if @SOPTYPE = 4 (select * from IV30302 where DOCNUMBR = @SOPNUMBE and DOCTYPE = 5)
End
-- shouldn't be any records in IV30400
if exists (select * from IV30500 where DOCNUMBR = @SOPNUMBE and IVDOCTYP in (5,6))
Begin
print 'IV30500 - Inventory Distribution History'
if @SOPTYPE = 3 (select * from IV30500 where DOCNUMBR = @SOPNUMBE and IVDOCTYP = 6)
if @SOPTYPE = 4 (select * from IV30500 where DOCNUMBR = @SOPNUMBE and IVDOCTYP = 5)
End
/*check if HITB table exists*/
if exists (select * from sysobjects where name = 'SEE30303')
Begin
if exists (select * from SEE30303 where DOCNUMBR = @SOPNUMBE and DOCTYPE in (5,6))
print 'SEE30303 - Inventory Transaction History Detail table (HITB)'
select * from SEE30303 where DOCNUMBR = @SOPNUMBE
End
/* Check if RED tables exist */
if exists (select * from sysobjects where name in ('PP000100', 'PP000101', 'PP100100', 'PP100101'))
Begin
print 'Revenue/Expense Deferrals'
print '=================================================================================='
print ''
if exists (select PP_Document_Number, 1 as Status, TRXAMNT
from PP000100 where PP_Document_Number = @SOPNUMBE
union select PP_Document_Number, 3 as Status, TRXAMNT
from PP100100 where PP_Document_Number = @SOPNUMBE)
print 'PP000100/PP100100 - Deferral Header Work/Deferral Header History'
select PP_Document_Number, 1 as Status, TRXAMNT
from PP000100 where PP_Document_Number = @SOPNUMBE
union
select PP_Document_Number, 3 as Status, TRXAMNT
from PP100100 where PP_Document_Number = @SOPNUMBE
if exists (select PP_Document_Number, 1 as Status, GLPOSTDT
from PP000101 where PP_Document_Number = @SOPNUMBE
union select PP_Document_Number, 3 as Status, GLPOSTDT
from PP100101 where PP_Document_Number = @SOPNUMBE)
print 'PP000101/PP100101 - Deferral Line Work/Deferral Line History'
select PP_Document_Number, 1 as Status, GLPOSTDT
from PP000101 where PP_Document_Number = @SOPNUMBE
union
select PP_Document_Number, 3 as Status, GLPOSTDT
from PP100101 where PP_Document_Number = @SOPNUMBE
End
print 'Receivables'
print '=================================================================================='
print ''
if exists (select * from RM00401 where DOCNUMBR = @SOPNUMBE and RMDTYPAL in (1,8))
Begin
print 'RM00401 - RM Key File'
if @SOPTYPE = 3 (select * from RM00401 where DOCNUMBR = @SOPNUMBE and RMDTYPAL = 1)
if @SOPTYPE = 4 (select * from RM00401 where DOCNUMBR = @SOPNUMBE and RMDTYPAL = 8)
End
if exists (select DOCNUMBR, RMDTYPAL, 2 as Status, CURTRXAM, CUSTNMBR, TRXSORCE, DOCDATE, ORTRXAMT
from RM20101 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE
union select DOCNUMBR, RMDTYPAL, 3 as Status, CUSTNMBR, TRXSORCE, DOCDATE, ORTRXAMT, CURTRXAM
from RM30101 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE)
Begin
print 'RM20101/RM30101 - RM Open / RM History'
select DOCNUMBR, RMDTYPAL, 2 as Status, CUSTNMBR, TRXSORCE, DOCDATE, ORTRXAMT, CURTRXAM
from RM20101 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE
union select DOCNUMBR, RMDTYPAL, 3 as Status, CUSTNMBR, TRXSORCE, DOCDATE, ORTRXAMT, CURTRXAM
from RM30101 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE
End
if exists (select DOCNUMBR, RMDTYPAL, 2 as Status, CUSTNMBR, TRXSORCE
from RM10101 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE
union select DOCNUMBR, RMDTYPAL, 3 as Status, CUSTNMBR, TRXSORCE
from RM30301 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE)
Begin
print 'RM10101/RM30301 - RM Dist Open / RM Dist History'
select DOCNUMBR, RMDTYPAL, 2 as Status, CUSTNMBR, TRXSORCE
from RM10101 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE
union select DOCNUMBR, RMDTYPAL, 3 as Status, CUSTNMBR, TRXSORCE
from RM30301 where RMDTYPAL in (1,8) and DOCNUMBR = @SOPNUMBE
End
if exists (select * from RM20201 where APTODCNM = @SOPNUMBE and APTODCTY in (1,8)
union
select * from RM30201 where APTODCNM = @SOPNUMBE and APTODCTY in (1,8))
Begin
Print 'RM20201/RM30201 - RM Apply Open/ RM Apply History'
select * from RM20201 where APTODCNM = @SOPNUMBE and APTODCTY in (1,8)
union
select * from RM30201 where APTODCNM = @SOPNUMBE and APTODCTY in (1,8)
End
print 'GL'
print '=================================================================================='
print ''
if exists (select ORDOCNUM from GL10001 where ORDOCNUM = @SOPNUMBE
union select ORDOCNUM from GL20000 where SERIES = 3 and ORDOCNUM = @SOPNUMBE
union select ORDOCNUM from GL30000 where SERIES = 3 and ORDOCNUM = @SOPNUMBE)
Begin
print 'GL10001/GL20000/GL30000 - GL work / GL open / GL history'
select ORDOCNUM, JRNENTRY, 1 as Status from GL10001 where ORDOCNUM = @SOPNUMBE
union select ORDOCNUM, JRNENTRY, 2 as Status from GL20000 where SERIES = 3 and ORDOCNUM = @SOPNUMBE
union select ORDOCNUM, JRNENTRY, 3 as Status from GL30000 where SERIES = 3 and ORDOCNUM = @SOPNUMBE
End
print 'Bank Rec'
print '=================================================================================='
print ''
if exists (select * from CM20100 where AUDITTRAIL in (select TRXSORCE from SOP30200 where SOPNUMBE = @SOPNUMBE))
Begin
print 'CM20100 - CM Journal'
select * from CM20100 where AUDITTRAIL in (select TRXSORCE from SOP30200 where SOPNUMBE = @SOPNUMBE)
End
if exists (select * from CM20300 where SRCDOCNUM = @SOPNUMBE)
Begin
print 'CM20300 - CM Receipt'
select * from CM20300 where SRCDOCNUM = @SOPNUMBE
End
/*check if HITB table exists*/
if exists (select * from sysobjects where name = 'SVC00600')
Begin
print 'Contract Administration'
print '=================================================================================='
print ''
if exists (select * from SVC00600 where Source_Contract_Number = @SOPNUMBE)
print 'SVC00600 - Contract Header'
select * from SVC00600 where Source_Contract_Number = @SOPNUMBE
End
Regards,
Kirk
This worked and thank you for the follow up.
Hello, though it is messed up, I think it can be resolved by the following SQL query.
Look into the details of the following detail -
Select * from Sop10100 where sopnumbe = 'Your_order'
Select * from Sop10200 where sopnumbe ='Your_order'
Select * from Sop10103 where sopnumbe = 'Your_order'
Take a backup of the two tables ( the header and the payment one)
Select * into Sop10103_sk_backup_08242016 from Sop10103
Select * into Sop10100_sk_backup_08242016 from Sop10100
Delete the payment record from the SOP10103 table using this query -
Delete Sop10103 where sopnumbe ='Your_order'
Update the following in your header table -
Update Sop10100
set
DEPRECVD = 0,
ORDEPRVD = 0,
ACCTAMNT = type here the total amount of the invoice,
ORACTAMT = type here the total amount of the invoice
where Sopnumbe = 'Your_order'
//please look for currencies or originating and functional amount
This will let you delete the order from GP. But remember you have to deal with the deposit in financial module and in the bank transactions which would be easier to deal with.
Please let me know if it helps.
Satyendra
Hi Deborah,
Were you ever able to get these transactions voided without opening the closed fiscal period? We have a similar problem with deposits that were applied (no monies collected) from a few years ago and we can't remove them because the fiscal period is not open. Re-Opening the fiscal period (2013) is not an option for us.
Thanks,
Ben
Hi Rosemary,
These are not posted documents and yes, the ‘void’ option is available within the SOP transaction screen.
The problem is, when I select “void”, it tells me 'You can’t void a document containing posted deposits'.
Then, if I try to remove the deposit, it tells me I can’t do that for a closed period:
"The fiscal period (s) for the transaction(s) you are attempting to insert or remove has been closed. To continue, reopen the fiscal period(s).
I am unable to open the periods in question because they have been closed out and through a final review.
Like I said these are from a few years ago, so it I am unable to open the period to remove the cash deposit.
Any suggestions would be appreciated.
Deb
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (