Cancelling mass Invoices

[L809D]

Hi Gurus

We are trying to find if there is a way to cancel mass invoices from applications or from the backend. We have about 20000 invoices that we want to cancel but since in applications you can cancel not so many invoices at a time; we are trying to find out if there is any other way to do this.
We were planning to do an update from the backend and set these invoices to cancelled status but after checking the trace file from the cancel process it appears this process calls alot of packages and updates alot of tables which makes the idea of updating the status to cancelled complicated.

Thanks in advance

Leo

[kommineni7]

i have another quesiton on the same query.

if we delete those invoices what about accoutning effect. what about supplier/customer balances , suppose if that invoice is not paid/recieved but accounted?

Thanks,
Venkatesh

[L809D]

These invoices are validated but not yet paid. We don't want to delete them but cancel.
For accounting you have to run Accounting Process SRS in Payables to create accounting for the transaction. W

Leo

[ramkic]

Hi,
Please use "AP_CANCEL_PKG" to cancel the Invoices. This API will internally call all the related processes for the integration.
This will work for you. Good Luck

Thanks
Best Regards
ramkic

[L809D]

I already tried to call that pkg but it appears like the process first calls AP_Utilities_pkg and Ap_invoices_pkg and it also calls ap_payment_schedules_pkg.

I tried to call the package is follows:

i_success:= ap_cancel_pkg.Ap_Cancel_Single_Invoice which is the function that cancels single invoices.

Do u have another way you can suggest that I call the pkg.

Thanks again for the help.

Leo

[ramkic]

Hi,
The "ap_cancel_pkg" calls the mentioned packages to check whether the cancelling invoice has been accounted, paid or applied on any oyhe invoices. If your invoice is only validated then I think there is no problem with using this package. Do you receive any issues or errors while using this package?

Thanks
Best Regards
ramkic

[L809D]

Ramkic,

Maybe I'm having issues calling it. How can I test this package manually ? I tried to cancel one invoice manually using this package but it didn't cancel the invoice that I had hardcoded onto the parameter list. I will follow up with a sample code that I used shortly.

Thanks for the follow up.
Leo

[L809D]

Here is the PL/SQL block that I'm hardcoding my invoice values for testing and I have pasted the error that I'm getting.

DECLARE
P_INVOICE_ID NUMBER;
P_LAST_UPDATED_BY NUMBER;
P_LAST_UPDATE_LOGIN NUMBER;
P_SET_OF_BOOKS_ID NUMBER;
P_ACCOUNTING_DATE DATE;
P_PERIOD_NAME VARCHAR2(200);
P_MESSAGE_NAME VARCHAR2(200);
P_INVOICE_AMOUNT NUMBER;
P_BASE_AMOUNT NUMBER;
P_TAX_AMOUNT NUMBER;
P_TEMP_CANCELLED_AMOUNT NUMBER;
P_CANCELLED_BY NUMBER;
P_CANCELLED_AMOUNT NUMBER;
P_CANCELLED_DATE DATE;
P_LAST_UPDATE_DATE DATE;
P_ORIGINAL_PREPAYMENT_AMOUNT NUMBER;
P_CHECK_ID NUMBER;
P_PAY_CURR_INVOICE_AMOUNT NUMBER;
P_CALLING_SEQUENCE VARCHAR2(200);
v_Return BOOLEAN;
BEGIN
P_INVOICE_ID := NULL;
P_LAST_UPDATED_BY := NULL;
P_LAST_UPDATE_LOGIN := NULL;
P_SET_OF_BOOKS_ID := NULL;
P_ACCOUNTING_DATE := NULL;
P_PERIOD_NAME := NULL;
P_CHECK_ID := NULL;
P_CALLING_SEQUENCE := NULL;

v_Return := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE(
P_INVOICE_ID => '777526',
P_LAST_UPDATED_BY => '1313',
P_LAST_UPDATE_LOGIN => NULL,
P_SET_OF_BOOKS_ID => '1',
P_ACCOUNTING_DATE => '08/31/2004 22:39:43',
P_PERIOD_NAME => '',
P_MESSAGE_NAME => P_MESSAGE_NAME,
P_INVOICE_AMOUNT => P_INVOICE_AMOUNT,
P_BASE_AMOUNT => P_BASE_AMOUNT,
P_TAX_AMOUNT => P_TAX_AMOUNT,
P_TEMP_CANCELLED_AMOUNT => P_TEMP_CANCELLED_AMOUNT,
P_CANCELLED_BY => P_CANCELLED_BY,
P_CANCELLED_AMOUNT => P_CANCELLED_AMOUNT,
P_CANCELLED_DATE => P_CANCELLED_DATE,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_ORIGINAL_PREPAYMENT_AMOUNT => P_ORIGINAL_PREPAYMENT_AMOUNT,
P_CHECK_ID => NULL,
P_PAY_CURR_INVOICE_AMOUNT => P_PAY_CURR_INVOICE_AMOUNT,
P_CALLING_SEQUENCE => NULL
);
DBMS_OUTPUT.PUT_LINE('P_MESSAGE_NAME = ' || P_MESSAGE_NAME);
DBMS_OUTPUT.PUT_LINE('P_INVOICE_AMOUNT = ' || P_INVOICE_AMOUNT);
DBMS_OUTPUT.PUT_LINE('P_BASE_AMOUNT = ' || P_BASE_AMOUNT);
DBMS_OUTPUT.PUT_LINE('P_TAX_AMOUNT = ' || P_TAX_AMOUNT);
DBMS_OUTPUT.PUT_LINE('P_TEMP_CANCELLED_AMOUNT = ' || P_TEMP_CANCELLED_AMOUNT);
DBMS_OUTPUT.PUT_LINE('P_CANCELLED_BY = ' || P_CANCELLED_BY);
DBMS_OUTPUT.PUT_LINE('P_CANCELLED_AMOUNT = ' || P_CANCELLED_AMOUNT);
DBMS_OUTPUT.PUT_LINE('P_CANCELLED_DATE = ' || P_CANCELLED_DATE);
DBMS_OUTPUT.PUT_LINE('P_LAST_UPDATE_DATE = ' || P_LAST_UPDATE_DATE);
DBMS_OUTPUT.PUT_LINE('P_ORIGINAL_PREPAYMENT_AMOUNT = ' || P_ORIGINAL_PREPAYMENT_AMOUNT);
DBMS_OUTPUT.PUT_LINE('P_PAY_CURR_INVOICE_AMOUNT = ' || P_PAY_CURR_INVOICE_AMOUNT);
--DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;


BELOW IS THE ERROR THAT I'M GETTING.

Connecting to the database Mahwah Dev.
ORA-01843: not a valid month
ORA-06512: at line 32
Process exited.
Disconnecting from the database Mahwah Dev.

Thanks alot.

Leo

[admin]

The problem is with your date format,

Change line,

P_ACCOUNTING_DATE => '08/31/2004 22:39:43',

to

P_ACCOUNTING_DATE => to_date('2004/08/31 22:39:43' ,'YYYY/MM/DD HH24:MI:SS')

Hope will work if still any issue then let us know. thanks

[L809D]

After I did correct date format it appears like it's calling utilities package for GL_open_periods which is why it's giving an uotput as below.

Connecting to the database Mahwah Dev.
P_MESSAGE_NAME = AP_DISTS_NO_OPEN_FUT_PERIOD
P_INVOICE_AMOUNT =
P_BASE_AMOUNT =
P_TAX_AMOUNT =
P_TEMP_CANCELLED_AMOUNT =
P_CANCELLED_BY =
P_CANCELLED_AMOUNT =
P_CANCELLED_DATE =
P_LAST_UPDATE_DATE =
P_ORIGINAL_PREPAYMENT_AMOUNT =
P_PAY_CURR_INVOICE_AMOUNT =
Process exited.
Disconnecting from the database Mahwah Dev.


On P_message_name

[ramkic]

Hi,
Change the "Accounting Date" parameter value to an Open or Future Period, since there is a Open Period Validation in the process. You are supplying a closed period date.

Thanks
Regards
ramkic

[L809D]

I did open the period from the GL and AP but it still gives the same message.

[ramkic]

Hi,
Did you open period for the accounting date that you are providing? For example: your accounting period date is 01-AUG-2004 then open period AUG-04 (as per the setup).
Try to use the latest open period date, rather than opening an already closed very old accounting peroiod. Also provide the date as 06/01/07(MM/DD/YY).

Good Luck.
Regards
ramkic

[L809D]

Do I really have to pass the period_name input parameter or I can just leave it NULL?

[ramkic]

You can either pass one of the parameters or both. It doesn't matter/ but shuld be in open or future period.

Best Regards
ramkic