API Script to Cancel AP Invoices (Cancel AP Invoice API )

API Script to Cancel AP Invoices (Cancel AP Invoice API )

/* Formatted on 2017/07/08 18:15 (Formatter Plus v4.8.0) */

CREATE OR REPLACE PROCEDURE cancel_invoices (ip_operating_unit IN VARCHAR2)

AS

 l_resp_id NUMBER;

 l_appl_id NUMBER;

 l_user_id NUMBER := apps.fnd_global.user_id;

 l_org_id NUMBER := apps.fnd_global.org_id;

 l_message_name VARCHAR2 (1000);

 l_invoice_amount NUMBER;

 l_base_amount NUMBER;

 l_tax_amount NUMBER;

 l_temp_cancelled_amount NUMBER;

 l_cancelled_by VARCHAR2 (1000);

 l_cancelled_amount NUMBER;

 l_cancelled_date DATE;

 l_last_update_date DATE;

 l_original_prepayment_amount NUMBER;

 l_pay_curr_invoice_amount NUMBER;

 l_token VARCHAR2 (100);

 l_boolean BOOLEAN;

 err_msg VARCHAR2 (2000);

 CURSOR invoice_cur

 IS

 SELECT aia.invoice_id, aia.last_updated_by, aia.last_update_login,

 aia.gl_date, aia.invoice_num

 FROM xx_ap_invoices_conv_stg a,

 ap_invoices_all aia,

 ap_invoice_lines_all aila

 WHERE a.ls_inv_num = aia.invoice_num

 AND a.ls_org_id = aia.org_id

 AND aia.invoice_id = aila.invoice_id

 AND aia.org_id = aila.org_id

 AND aia.payment_status_flag = 'N'

 AND NVL (aila.cancelled_flag, 'N') <> 'Y';

--- AND aia.invoice_num = '65431';

BEGIN

 BEGIN

 SELECT DISTINCT fr.responsibility_id, frx.application_id

 INTO l_resp_id, l_appl_id

 FROM apps.fnd_responsibility frx,

 apps.fnd_responsibility_tl fr

 WHERE fr.responsibility_id = frx.responsibility_id

 AND UPPER (fr.responsibility_name) LIKE

 UPPER (DECODE (ip_operating_unit,

 'OU USA MA', 'Payables Manager',

 'OU USA WI', 'OU USA WI_Payables Manager',

 'OU Austria', 'OU AUSTRIA_Payables Manager',

 'OU China', 'OU CHINA_Payables Manager'

 )

 );

 DBMS_OUTPUT.put_line ('l_resp_id => ' || l_resp_id);

 DBMS_OUTPUT.put_line ('l_appl_id => ' || l_appl_id);

 EXCEPTION

 WHEN OTHERS

 THEN

 err_msg :=

 'Error Occured while Deriving responsibility id' || SQLERRM;

 apps.fnd_file.put_line

 (apps.fnd_file.output,

 'Error Occured while Deriving responsibility id'

 );

 END;

 mo_global.set_policy_context ('S', l_org_id);

 apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

 FOR l_inv_rec IN invoice_cur ----(l_org_id)

 LOOP

 DBMS_OUTPUT.put_line

 ( 'Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: '

 || l_inv_rec.invoice_num

 );

 DBMS_OUTPUT.put_line

 ('**************************************************************');

 l_boolean :=

 ap_cancel_pkg.ap_cancel_single_invoice

 (p_invoice_id => l_inv_rec.invoice_id,

 p_last_updated_by => l_inv_rec.last_updated_by,

 p_last_update_login => l_inv_rec.last_update_login,

 p_accounting_date => l_inv_rec.gl_date,

 p_message_name => l_message_name,

 p_invoice_amount => l_invoice_amount,

 p_base_amount => l_base_amount,

 p_temp_cancelled_amount => l_temp_cancelled_amount,

 p_cancelled_by => l_cancelled_by,

 p_cancelled_amount => l_cancelled_amount,

 p_cancelled_date => l_cancelled_date,

 p_last_update_date => l_last_update_date,

 p_original_prepayment_amount => l_original_prepayment_amount,

 p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,

 p_token => l_token,

 p_calling_sequence => NULL

 );

 DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);

 DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);

 DBMS_OUTPUT.put_line ('l_base_amount => ' || l_base_amount);

 DBMS_OUTPUT.put_line ('l_tax_amount => ' || l_tax_amount);

 DBMS_OUTPUT.put_line ( 'l_temp_cancelled_amount => '

 || l_temp_cancelled_amount

 );

 DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);

 DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);

 DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);

 DBMS_OUTPUT.put_line ('P_last_update_date => ' || l_last_update_date);

 DBMS_OUTPUT.put_line ( 'P_original_prepayment_amount => '

 || l_original_prepayment_amount

 );

 DBMS_OUTPUT.put_line ( 'l_pay_curr_invoice_amount => '

 || l_pay_curr_invoice_amount

 );

 IF l_boolean

 THEN

 DBMS_OUTPUT.put_line ( 'Successfully Cancelled the Invoice => '

 || l_inv_rec.invoice_num

 );

 COMMIT;

 ELSE

 DBMS_OUTPUT.put_line ( 'Failed to Cancel the Invoice => '

 || l_inv_rec.invoice_num

 );

 ROLLBACK;

 END IF;

 END LOOP;

END cancel_invoices;