Open Forum

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec
------------------------------

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX
------------------------------

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec
------------------------------

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Manuel,

I wouldn't do the fix using manual check entry.

After using transaction import for Voucher and Adjustment and releasing the batch, I would dump the the AP aging into a spreadsheet.  By doing this, you could go to payment selection and change to Reference number., you could copy and paste the reference number into the line detail.  Then open Edit / Select Document for Payment, call up the batch to make sure it totals 0.  If it does, you know you got everything and then can print the checks.

I believe I have a CTL file and example import spreadsheet that I can share if that helps.  If you're decent in Excel, probably take about an hour or so to create the spreadsheet for import.  Once imported, creating the check run is probably about 10 minutes or so.  So 1 to 1.5 hours of time gets you where you need to be and have a flow / audit  trail.

Just my 2 cents  :)

Thanks,
Michael



------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX
------------------------------

Original Message:
Sent: Feb 01, 2023 01:38 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hello Michael,

Thank you for your time, and of course I accept the CTL and Excel to make the necessary adjustments


of course your 2 cents and many likes :)

------------------------------
Manuel Vela
Tec
------------------------------

Original Message:
Sent: Feb 01, 2023 02:05 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I wouldn't do the fix using manual check entry.

After using transaction import for Voucher and Adjustment and releasing the batch, I would dump the the AP aging into a spreadsheet.  By doing this, you could go to payment selection and change to Reference number., you could copy and paste the reference number into the line detail.  Then open Edit / Select Document for Payment, call up the batch to make sure it totals 0.  If it does, you know you got everything and then can print the checks.

I believe I have a CTL file and example import spreadsheet that I can share if that helps.  If you're decent in Excel, probably take about an hour or so to create the spreadsheet for import.  Once imported, creating the check run is probably about 10 minutes or so.  So 1 to 1.5 hours of time gets you where you need to be and have a flow / audit  trail.

Just my 2 cents  :)

Thanks,
Michael



------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 01:38 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Manuel,

Attached are the CSV and CTL files needed.  You will need to populate the CSV accordingly.  I have put the description of what is needed in the field.  

For tran type - VO is voucher and DA is debit adjustment depending on which way the document needs to go.

Once you populate the CSV, you can do the transaction import in SL.

Let me know of any issues or questions.  I can do a GTM if you need any assistance.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX
------------------------------

Original Message:
Sent: Feb 01, 2023 02:15 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello Michael,

Thank you for your time, and of course I accept the CTL and Excel to make the necessary adjustments


of course your 2 cents and many likes :)

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:05 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I wouldn't do the fix using manual check entry.

After using transaction import for Voucher and Adjustment and releasing the batch, I would dump the the AP aging into a spreadsheet.  By doing this, you could go to payment selection and change to Reference number., you could copy and paste the reference number into the line detail.  Then open Edit / Select Document for Payment, call up the batch to make sure it totals 0.  If it does, you know you got everything and then can print the checks.

I believe I have a CTL file and example import spreadsheet that I can share if that helps.  If you're decent in Excel, probably take about an hour or so to create the spreadsheet for import.  Once imported, creating the check run is probably about 10 minutes or so.  So 1 to 1.5 hours of time gets you where you need to be and have a flow / audit  trail.

Just my 2 cents  :)

Thanks,
Michael



------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 01:38 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Michael,


I had to rebuild the CTL file, I´m using SL 7 FP4,
I have a question,
how can I force the CTL so that when importing it does not insert the tax code that the vendor has?
I attach my csv and ctl (txt) files, you can include a parameter in the instruction
serr = SetObjectValue( "ctaxiddflt", ImportField(4) ) so that the value is null

I really apreciate your help


------------------------------
Manuel Vela
Tec
------------------------------

Original Message:
Sent: Feb 01, 2023 02:59 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Attached are the CSV and CTL files needed.  You will need to populate the CSV accordingly.  I have put the description of what is needed in the field.  

For tran type - VO is voucher and DA is debit adjustment depending on which way the document needs to go.

Once you populate the CSV, you can do the transaction import in SL.

Let me know of any issues or questions.  I can do a GTM if you need any assistance.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 02:15 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello Michael,

Thank you for your time, and of course I accept the CTL and Excel to make the necessary adjustments


of course your 2 cents and many likes :)

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:05 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I wouldn't do the fix using manual check entry.

After using transaction import for Voucher and Adjustment and releasing the batch, I would dump the the AP aging into a spreadsheet.  By doing this, you could go to payment selection and change to Reference number., you could copy and paste the reference number into the line detail.  Then open Edit / Select Document for Payment, call up the batch to make sure it totals 0.  If it does, you know you got everything and then can print the checks.

I believe I have a CTL file and example import spreadsheet that I can share if that helps.  If you're decent in Excel, probably take about an hour or so to create the spreadsheet for import.  Once imported, creating the check run is probably about 10 minutes or so.  So 1 to 1.5 hours of time gets you where you need to be and have a flow / audit  trail.

Just my 2 cents  :)

Thanks,
Michael



------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 01:38 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Manuel,

I believe you probably have a tax setup called Exempt or None which would mean 0 tax calculation.

The easiest way would be to added to the CTL file a line that sets field to equal whatever that 0 tax filed is called in your system.  This would be added to level 4  Change Exempt to whatever your 0 tax ID is....

serr = SetObjectValue( "ctaxiddflt", "Exempt" )

Let me know if this doesn't fix the issue.

Thanks,
Michael




------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX
------------------------------

Original Message:
Sent: Feb 01, 2023 05:56 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael,


I had to rebuild the CTL file, I´m using SL 7 FP4,
I have a question,
how can I force the CTL so that when importing it does not insert the tax code that the vendor has?
I attach my csv and ctl (txt) files, you can include a parameter in the instruction
serr = SetObjectValue( "ctaxiddflt", ImportField(4) ) so that the value is null

I really apreciate your help

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:59 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Attached are the CSV and CTL files needed.  You will need to populate the CSV accordingly.  I have put the description of what is needed in the field.  

For tran type - VO is voucher and DA is debit adjustment depending on which way the document needs to go.

Once you populate the CSV, you can do the transaction import in SL.

Let me know of any issues or questions.  I can do a GTM if you need any assistance.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 02:15 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello Michael,

Thank you for your time, and of course I accept the CTL and Excel to make the necessary adjustments


of course your 2 cents and many likes :)

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:05 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I wouldn't do the fix using manual check entry.

After using transaction import for Voucher and Adjustment and releasing the batch, I would dump the the AP aging into a spreadsheet.  By doing this, you could go to payment selection and change to Reference number., you could copy and paste the reference number into the line detail.  Then open Edit / Select Document for Payment, call up the batch to make sure it totals 0.  If it does, you know you got everything and then can print the checks.

I believe I have a CTL file and example import spreadsheet that I can share if that helps.  If you're decent in Excel, probably take about an hour or so to create the spreadsheet for import.  Once imported, creating the check run is probably about 10 minutes or so.  So 1 to 1.5 hours of time gets you where you need to be and have a flow / audit  trail.

Just my 2 cents  :)

Thanks,
Michael



------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 01:38 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Michael,

I really appreciate your time and your help, I learned many details that I did not know. what i did was set this line:
serr = SetObjectValue( "ctaxiddflt"," " )
with an espace and works fine.

when i set serr = SetObjectValue( "ctaxiddflt", "Exempt" ) send error message

have a great weekend and once again thank you
Saludos..

------------------------------
Manuel Vela
Tec
------------------------------

Original Message:
Sent: Feb 03, 2023 09:47 AM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I believe you probably have a tax setup called Exempt or None which would mean 0 tax calculation.

The easiest way would be to added to the CTL file a line that sets field to equal whatever that 0 tax filed is called in your system.  This would be added to level 4  Change Exempt to whatever your 0 tax ID is....

serr = SetObjectValue( "ctaxiddflt", "Exempt" )

Let me know if this doesn't fix the issue.

Thanks,
Michael




------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 05:56 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael,


I had to rebuild the CTL file, I´m using SL 7 FP4,
I have a question,
how can I force the CTL so that when importing it does not insert the tax code that the vendor has?
I attach my csv and ctl (txt) files, you can include a parameter in the instruction
serr = SetObjectValue( "ctaxiddflt", ImportField(4) ) so that the value is null

I really apreciate your help

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:59 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Attached are the CSV and CTL files needed.  You will need to populate the CSV accordingly.  I have put the description of what is needed in the field.  

For tran type - VO is voucher and DA is debit adjustment depending on which way the document needs to go.

Once you populate the CSV, you can do the transaction import in SL.

Let me know of any issues or questions.  I can do a GTM if you need any assistance.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 02:15 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello Michael,

Thank you for your time, and of course I accept the CTL and Excel to make the necessary adjustments


of course your 2 cents and many likes :)

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:05 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I wouldn't do the fix using manual check entry.

After using transaction import for Voucher and Adjustment and releasing the batch, I would dump the the AP aging into a spreadsheet.  By doing this, you could go to payment selection and change to Reference number., you could copy and paste the reference number into the line detail.  Then open Edit / Select Document for Payment, call up the batch to make sure it totals 0.  If it does, you know you got everything and then can print the checks.

I believe I have a CTL file and example import spreadsheet that I can share if that helps.  If you're decent in Excel, probably take about an hour or so to create the spreadsheet for import.  Once imported, creating the check run is probably about 10 minutes or so.  So 1 to 1.5 hours of time gets you where you need to be and have a flow / audit  trail.

Just my 2 cents  :)

Thanks,
Michael



------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 01:38 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------

Hi Manuel,

Yeah, after I sent it, I thought of just leaving blank with " "  lol

Glad everything worked out for you.  Have a great weekend!!

Best
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX
------------------------------

Original Message:
Sent: Feb 03, 2023 10:43 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael,

I really appreciate your time and your help, I learned many details that I did not know. what i did was set this line:
serr = SetObjectValue( "ctaxiddflt"," " )
with an espace and works fine.

when i set serr = SetObjectValue( "ctaxiddflt", "Exempt" ) send error message

have a great weekend and once again thank you
Saludos..

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 03, 2023 09:47 AM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I believe you probably have a tax setup called Exempt or None which would mean 0 tax calculation.

The easiest way would be to added to the CTL file a line that sets field to equal whatever that 0 tax filed is called in your system.  This would be added to level 4  Change Exempt to whatever your 0 tax ID is....

serr = SetObjectValue( "ctaxiddflt", "Exempt" )

Let me know if this doesn't fix the issue.

Thanks,
Michael




------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 05:56 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael,


I had to rebuild the CTL file, I´m using SL 7 FP4,
I have a question,
how can I force the CTL so that when importing it does not insert the tax code that the vendor has?
I attach my csv and ctl (txt) files, you can include a parameter in the instruction
serr = SetObjectValue( "ctaxiddflt", ImportField(4) ) so that the value is null

I really apreciate your help

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:59 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Attached are the CSV and CTL files needed.  You will need to populate the CSV accordingly.  I have put the description of what is needed in the field.  

For tran type - VO is voucher and DA is debit adjustment depending on which way the document needs to go.

Once you populate the CSV, you can do the transaction import in SL.

Let me know of any issues or questions.  I can do a GTM if you need any assistance.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 02:15 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello Michael,

Thank you for your time, and of course I accept the CTL and Excel to make the necessary adjustments


of course your 2 cents and many likes :)

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 02:05 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

I wouldn't do the fix using manual check entry.

After using transaction import for Voucher and Adjustment and releasing the batch, I would dump the the AP aging into a spreadsheet.  By doing this, you could go to payment selection and change to Reference number., you could copy and paste the reference number into the line detail.  Then open Edit / Select Document for Payment, call up the batch to make sure it totals 0.  If it does, you know you got everything and then can print the checks.

I believe I have a CTL file and example import spreadsheet that I can share if that helps.  If you're decent in Excel, probably take about an hour or so to create the spreadsheet for import.  Once imported, creating the check run is probably about 10 minutes or so.  So 1 to 1.5 hours of time gets you where you need to be and have a flow / audit  trail.

Just my 2 cents  :)

Thanks,
Michael



------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 01:38 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Michael

Thank you for your quick response,
Of course what you are telling me would be the correct thing to maintain the integrity of the information, I already checked and there would be more than 200 manual check batches and obviously generate the adjustment movements for each vendor.
The problem is that they are pressuring me to correct this information so that it does not appear in the balance and balance age reports. That is why I find myself in need (although I know it is not the right thing to do) to correct these balances as quickly as possible.
I still get the question about the Status field "??" if I do it via SQL.


Thank you again from Mexico



------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 01:11 PM
From: Michael O'Neill
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi Manuel,

Since there is no need to create a 1 to 1 relationship between each document and a corresponding "fix" document, I'd do 1 of the 2 following suggestions.

If the 1000 plus documents is spread over only a few vendors, I'd create the "fix" document in AP to 0 out the amount and then do a zero check.

If it is a lot of vendors, I'd create a data dump that I could use in a transaction import to create the necessary "fix" document then do the zero check.

There is always the SQL approach, but I'm a big fan of flowing transactions thru SL as a fix before doing an SQL script.  This keeps the subledger and GL in sync as well.

Hope this helps.

Best,
Michael

------------------------------
Michael O'Neill
Director of Finance
Eastex Telephone Cooperative
Henderson TX

Original Message:
Sent: Feb 01, 2023 12:53 PM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hi all again,

I think the answer is this as mensioned @David Callery, i added the LUpd_DateTime to obtain the PerClosed as the last movement, but I have a doubt in Status Field.
Can someone clarify this question for me please?

I will appreciate your help
Gracias

SELECT VendId,RefNbr,DocType,PerPost,PerClosed,LUpd_DateTime,CuryId,OrigDocAmt,DocBal,CuryDocBal
FROM APDoc where OpenDoc = 1and CuryId='MN'
and DocBal between '0.01'and '1'

BEGIN TRAN
UPDATE APDoc set
PerClosed =CONVERT(nvarchar(6), LUpd_DateTime, 112),
CuryDocBal = 0,
DocBal = 0,
OpenDoc = 0,
Status=?
where RefNbr='XXXXX'and VendId ='XXXX'
ROLLBACK TRAN

​

------------------------------
Manuel Vela
Tec

Original Message:
Sent: Feb 01, 2023 11:32 AM
From: Manuel Vela
Subject: Small Balance/Credit Wirte Off in Accounts Payable

Hello dear SL bloggers,
Do you have a procedure similar to Small Balance/Credit Wirte Off in Accounts Receivable but for Accounts Payable?
This is because I have more than a thousand documents (VO, AC, DC, PP) with a balance of 0.0# in DocBal or CuryDocBal since I handle 2 currencies,

I hope you can help me with some SQL procedure since I cannot apply them since it tells me that the balance is zero.

Thanks to all and best regards

------------------------------
Manuel Vela
Tec
------------------------------