Oracle
Payable API's
API
to apply hold on AP invoice in R12 (AP_HOLDS_PKG.INSERT_SINGLE_HOLD)
Below script will help you apply a hold on AP
invoice in oracle apps R12
API:
AP_HOLDS_PKG.INSERT_SINGLE_HOLD
Test
Instance: R12.1.1
Tables
Affected: AP_HOLDS_ALL
Script:
SET serveroutput on;
DECLARE
x_invoice_id NUMBER;
x_hold_lookup_code VARCHAR2 (200);
x_hold_type VARCHAR2 (200);
x_hold_reason VARCHAR2 (200);
x_held_by NUMBER;
x_calling_sequence VARCHAR2 (200);
v_check_flag VARCHAR2 (1);
v_context VARCHAR2 (10);
FUNCTION set_context (
i_user_name IN VARCHAR2,
i_resp_name IN VARCHAR2,
i_org_id IN NUMBER
)
RETURN VARCHAR2
IS
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lang VARCHAR2 (100);
v_session_lang VARCHAR2 (100) := fnd_global.current_language;
v_return VARCHAR2 (10) := 'T';
v_nls_lang VARCHAR2 (100);
v_org_id NUMBER := i_org_id;
/* Cursor to get the user id information based on the input user
name */
CURSOR cur_user
IS
SELECT user_id
FROM fnd_user
WHERE user_name = i_user_name;
/* Cursor to get the responsibility information */
CURSOR cur_resp
IS
SELECT responsibility_id, application_id, LANGUAGE
FROM fnd_responsibility_tl
WHERE responsibility_name = i_resp_name;
/* Cursor to get the nls language information for setting the
language context */
CURSOR cur_lang (p_lang_code VARCHAR2)
IS
SELECT nls_language
FROM fnd_languages
WHERE language_code = p_lang_code;
BEGIN
/* To get the user id details */
OPEN cur_user;
FETCH cur_user
INTO v_user_id;
IF cur_user%NOTFOUND
THEN
v_return := 'F';
END IF; --IF
cur_user%NOTFOUND
CLOSE cur_user;
/* To get the responsibility and responsibility application id */
OPEN cur_resp;
FETCH cur_resp
INTO v_resp_id, v_resp_appl_id, v_lang;
IF cur_resp%NOTFOUND
THEN
v_return := 'F';
END IF; --IF
cur_resp%NOTFOUND
CLOSE cur_resp;
/* Setting the oracle applications context for the particular
session */
fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_resp_id,
resp_appl_id => v_resp_appl_id
);
/* Setting the org context for the particular session */
mo_global.set_policy_context ('S', v_org_id);
/* setting the nls context for the particular session */
IF v_session_lang != v_lang
THEN
OPEN cur_lang (v_lang);
FETCH cur_lang
INTO v_nls_lang;
CLOSE cur_lang;
fnd_global.set_nls_context (v_nls_lang);
END IF; --IF
v_session_lang != v_lang
RETURN v_return;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'F';
END set_context;
BEGIN
--1. Set applications context if not already set.
v_context := set_context ('&user', '&resp_name’, '&org_id');
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the
context');
END IF;
mo_global.init ('SQLAP');
x_invoice_id := 1425;
x_hold_lookup_code := 'LINE VARIANCE';
x_hold_type := 'LINE HOLD REASON';
x_hold_reason := 'Total of Invoice Lines does not equal Invoice Amount';
x_held_by := 5;
x_calling_sequence := NULL;
AP_HOLDS_PKG.INSERT_SINGLE_HOLD
(x_invoice_id => x_invoice_id,
x_hold_lookup_code => x_hold_lookup_code,
x_hold_type => x_hold_type,
x_hold_reason => x_hold_reason,
x_held_by => x_held_by,
x_calling_sequence => x_calling_sequence
);
BEGIN
SELECT 'Y'
INTO v_check_flag
FROM ap_holds_all
WHERE invoice_id = x_invoice_id AND hold_lookup_code = 'LINE VARIANCE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_check_flag := 'N';
END;
DBMS_OUTPUT.put_line ('Hold Applied (Y/N) : ' || v_check_flag);
END;
Below
script will explain you to cancel an invoice through API
This
script was tested in R12.1.1 instance.
SET SERVEROUTPUT ON;
DECLARE
v_invoice_id
NUMBER
:=
11844;
v_last_updated_by
VARCHAR2(20):= 1961;
v_last_update_login
VARCHAR2(20):= 504106;
v_accounting_date
DATE
:=
SYSDATE;
v_boolean
BOOLEAN;
v_error_code
VARCHAR2(100);
v_debug_info
VARCHAR2(1000);
v_message_name
VARCHAR2(1000);
v_invoice_amount
NUMBER;
v_base_amount
NUMBER;
v_tax_amount
NUMBER;
v_temp_cancelled_amount NUMBER;
v_cancelled_by
VARCHAR2(1000);
v_cancelled_amount
NUMBER;
v_cancelled_date
DATE;
v_last_update_date
DATE;
v_token
VARCHAR2(100);
v_orig_prepay_amt
NUMBER;
v_pay_cur_inv_amt
NUMBER;
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name
IN
VARCHAR2
,i_org_id
IN
NUMBER)
RETURN VARCHAR2
IS
BEGIN
NULL;
-- Inorder to reduce the content of the post I moved the
implementation part of this function to another post and it is available here
END set_context;
BEGIN
-- Setting the context
----
v_context :=
set_context ('&user', '&responsibility', 108);
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the context');
END IF;
--- context done
------------
DBMS_OUTPUT.put_line ('Calling API to check
whetehr the Invoice is canellable '
);
v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(
P_invoice_id => v_invoice_id,
P_error_code => v_error_code,
P_debug_info => v_debug_info,
P_calling_sequence => NULL);
IF v_boolean
THEN
DBMS_OUTPUT.put_line ('Invoice '||v_invoice_id|| ' is
cancellable' );
ELSE
DBMS_OUTPUT.put_line ('Invoice '||v_invoice_id|| ' is not
cancellable :'|| v_error_code );
END IF;
DBMS_OUTPUT.put_line ('Calling API to Cancel
Invoice' );
v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
(p_invoice_id
=>
v_invoice_id,
p_last_updated_by
=>
v_last_updated_by,
p_last_update_login => v_last_update_login,
p_accounting_date
=>
v_accounting_date,
p_message_name
=>
v_message_name,
p_invoice_amount
=>
v_invoice_amount,
p_base_amount
=>
v_base_amount,
p_temp_cancelled_amount => v_temp_cancelled_amount,
p_cancelled_by
=>
v_cancelled_by,
p_cancelled_amount => v_cancelled_amount,
p_cancelled_date
=>
v_cancelled_date,
p_last_update_date => v_last_update_date,
p_original_prepayment_amount => v_orig_prepay_amt,
p_pay_curr_invoice_amount => v_pay_cur_inv_amt,
P_Token
=>
v_token,
p_calling_sequence => NULL
);
IF v_boolean
THEN
DBMS_OUTPUT.put_line ('Successfully Cancelled
the Invoice' );
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Failed to Cancel the
Invoice' );
ROLLBACK;
END IF;
END;
API
to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)
Below
script will help you to release hold on AP invoice in oracle apps R12
API: AP_HOLDS_PKG.RELEASE_SINGLE_HOLD
Test
Instance:
R12.1.1
Tables
Affected:
AP_HOLDS_ALL
Script:
SET serveroutput on;
DECLARE
x_invoice_id NUMBER;
x_hold_lookup_code VARCHAR2 (200);
x_release_lookup_code VARCHAR2 (200);
x_held_by NUMBER;
x_calling_sequence VARCHAR2 (200);
v_context VARCHAR2 (10);
v_check_flag VARCHAR2 (1);
FUNCTION set_context (
i_user_name IN VARCHAR2,
i_resp_name IN VARCHAR2,
i_org_id IN NUMBER
)
RETURN VARCHAR2
IS
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lang VARCHAR2 (100);
v_session_lang VARCHAR2 (100) := fnd_global.current_language;
v_return VARCHAR2 (10) := 'T';
v_nls_lang VARCHAR2 (100);
v_org_id NUMBER := i_org_id;
/* Cursor to get the user id information based on the input user
name */
CURSOR cur_user
IS
SELECT user_id
FROM fnd_user
WHERE user_name = i_user_name;
/* Cursor to get the responsibility information */
CURSOR cur_resp
IS
SELECT responsibility_id, application_id, LANGUAGE
FROM fnd_responsibility_tl
WHERE responsibility_name = i_resp_name;
/* Cursor to get the nls language information for setting the
language context */
CURSOR cur_lang (p_lang_code VARCHAR2)
IS
SELECT nls_language
FROM fnd_languages
WHERE language_code = p_lang_code;
BEGIN
/* To get the user id details */
OPEN cur_user;
FETCH cur_user
INTO v_user_id;
IF cur_user%NOTFOUND
THEN
v_return := 'F';
END IF; --IF
cur_user%NOTFOUND
CLOSE cur_user;
/* To get the responsibility and responsibility application id */
OPEN cur_resp;
FETCH cur_resp
INTO v_resp_id, v_resp_appl_id, v_lang;
IF cur_resp%NOTFOUND
THEN
v_return := 'F';
END IF; --IF
cur_resp%NOTFOUND
CLOSE cur_resp;
/* Setting the oracle applications context for the particular
session */
fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_resp_id,
resp_appl_id => v_resp_appl_id
);
/* Setting the org context for the particular session */
mo_global.set_policy_context ('S', v_org_id);
/* setting the nls context for the particular session */
IF v_session_lang != v_lang
THEN
OPEN cur_lang (v_lang);
FETCH cur_lang
INTO v_nls_lang;
CLOSE cur_lang;
fnd_global.set_nls_context (v_nls_lang);
END IF; --IF
v_session_lang != v_lang
RETURN v_return;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'F';
END set_context;
BEGIN
--1. Set applications context if not already set.
v_context := set_context ('&user', '&respname', '&org_id');
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the
context');
END IF;
mo_global.init ('SQLAP');
x_invoice_id := 1425;
x_hold_lookup_code := 'LINE VARIANCE';
x_release_lookup_code := 'VARIANCE CORRECTED';
x_held_by := 5;
x_calling_sequence := NULL;
ap_holds_pkg.release_single_hold
(x_invoice_id => x_invoice_id,
x_hold_lookup_code => x_hold_lookup_code,
x_release_lookup_code => x_release_lookup_code,
x_held_by => x_held_by,
x_calling_sequence => x_calling_sequence
);
BEGIN
SELECT 'Y'
INTO v_check_flag
FROM ap_holds_all
WHERE invoice_id = x_invoice_id
AND release_lookup_code = 'VARIANCE CORRECTED';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_check_flag := 'N';
END;
DBMS_OUTPUT.put_line ('Hold Released (Y/N) : ' || v_check_flag);
END;
Credit
Memo Creation via API ( ar_credit_memo_api_pub.create_request )
Below Script can be used to create a
Credit memo against an Invoice. This script was tested in 11i instance.
SET serveroutput ON;
DECLARE
-- This script was
tested in 11i instance --
v_return_status VARCHAR2 (1);
p_count NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
v_request_id NUMBER;
v_context VARCHAR2 (2);
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
NULL;
--
Inorder to reduce the content of the post I moved the implementation part of
this function to another post and it is available here
END set_context;
BEGIN
-- Setting the context
----
v_context :=
set_context ('&user', '&responsibility', 2038);
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the context');
END IF;
DBMS_OUTPUT.put_line ('2');
--- context done
------------
DBMS_OUTPUT.put_line ('Invoking Credit Memo
Creation api');
ar_credit_memo_api_pub.create_request
(
-- standard api parameters
p_api_version
=>
1.0,
p_init_msg_list
=>
fnd_api.g_false,
p_commit
=>
fnd_api.g_false,
x_return_status
=>
v_return_status,
x_msg_count
=>
v_msg_count,
x_msg_data
=>
v_msg_data,
x_request_id
=>
v_request_id,
-- credit memo request parameters
p_customer_trx_id
=>
&inv_cust_trx_id,
p_line_credit_flag
=>
'N',
p_line_amount
=>
&return_amount,
p_tax_amount
=>
0,
p_cm_reason_code
=>
'RETURN',
p_skip_workflow_flag
=>
'Y',
p_batch_source_name
=>
'&batch_source_name',
p_credit_method_installments => NULL,
p_credit_method_rules
=>
NULL
);
DBMS_OUTPUT.put_line ('Message count ' || v_msg_count);
IF v_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || v_msg_data);
ELSIF v_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF
v_msg_data IS NULL
THEN
EXIT;
END
IF;
DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || v_msg_data);
END
LOOP;
END IF;
END;
API to release hold for oracle Payable
Invoice R12.1.3, the (Hold Name = Matching required),
Hold release name = Holds Quick
Released
CREATE OR REPLACE procedure APPS.XXSANTOSH_RELEASE_AP_HOLD
as
--##############################################################################--
--#
#--
--# Procedure Name :
XXSANTOSH_RELEASE_AP_HOLD #--
--#
#--
--# Description : It will release the hold from ap
invoice #--
--# 1. NEED TO PASS THE USER
NAME #--
--# 2. NEED TO PASS THE
RESPONSIBILITY NAME #--
--# 3.ORGANIZATION ID #--
--# 4. delete from
ap.ap_invoice_lines_interface
#--
--# 5. delete from
ap.ap_invoices_interface
#--
--#
#--
--# Initial Verion Created By Last_Update_Date #--
--# -------------- ---------- ---------------- #--
--# 1.0 Santosh 08-AUG-2016 #--
--# #--
--##############################################################################--
x_invoice_id NUMBER;
x_hold_lookup_code VARCHAR2 (200);
x_release_lookup_code VARCHAR2 (200);
x_held_by NUMBER;
x_calling_sequence VARCHAR2 (200);
v_context VARCHAR2 (10);
v_check_flag VARCHAR2 (1);
FUNCTION set_context (
i_user_name IN VARCHAR2,
i_resp_name IN VARCHAR2,
i_org_id IN NUMBER
)
RETURN VARCHAR2
IS
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lang VARCHAR2 (100);
v_session_lang VARCHAR2 (100) := fnd_global.current_language;
v_return VARCHAR2 (10) := 'T';
v_nls_lang VARCHAR2 (100);
v_org_id NUMBER := i_org_id;
/* Cursor to get the user id
information based on the input user name */
CURSOR cur_user
IS
SELECT user_id
FROM fnd_user
WHERE user_name = i_user_name; ---------------Pass the user name
/* Cursor to get the
responsibility information */
CURSOR cur_resp
IS
SELECT responsibility_id, application_id, LANGUAGE
FROM fnd_responsibility_tl
WHERE responsibility_name = i_resp_name;
/* Cursor to get the nls
language information for setting the language context */
CURSOR cur_lang (p_lang_code VARCHAR2)
IS
SELECT nls_language
FROM fnd_languages
WHERE language_code = p_lang_code;
BEGIN
/* To get the user id details */
OPEN cur_user;
FETCH cur_user
INTO v_user_id;
IF cur_user%NOTFOUND
THEN
v_return := 'F';
END IF; --IF cur_user%NOTFOUND
CLOSE cur_user;
/* To get the responsibility and responsibility
application id */
OPEN cur_resp;
FETCH cur_resp
INTO v_resp_id, v_resp_appl_id,
v_lang;
IF cur_resp%NOTFOUND
THEN
v_return := 'F';
END IF; --IF cur_resp%NOTFOUND
CLOSE cur_resp;
/* Setting the oracle applications context for the
particular session */
fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_resp_id,
resp_appl_id => v_resp_appl_id
);
/* Setting the org context for the particular
session */
mo_global.set_policy_context ('S', v_org_id);
/* setting the nls context for the particular
session */
IF v_session_lang != v_lang
THEN
OPEN cur_lang (v_lang);
FETCH cur_lang
INTO v_nls_lang;
CLOSE cur_lang;
fnd_global.set_nls_context (v_nls_lang);
END IF; --IF v_session_lang != v_lang
RETURN v_return;
EXCEPTION
WHEN OTHERS
THEN
RETURN 'F';
END set_context;
BEGIN
--1. Set applications context
if not already set.
v_context := set_context ('&user', '&respname', '&org_id');
IF v_context = 'F'
THEN
DBMS_OUTPUT.put_line ('Error while setting the
context');
END IF;
mo_global.init ('SQLAP');
x_invoice_id := 908627; ------------Pass the Invoice ID
x_hold_lookup_code := 'PO REQUIRED'; ----------Pass the hold lookup code
x_release_lookup_code := 'HOLDS QUICK RELEASED'; --------Pass the release code
x_held_by := 5;
x_calling_sequence := NULL;
ap_holds_pkg.release_single_hold
(x_invoice_id
=> x_invoice_id,
x_hold_lookup_code => x_hold_lookup_code,
x_release_lookup_code => x_release_lookup_code,
x_held_by => x_held_by,
x_calling_sequence => x_calling_sequence
);
BEGIN
SELECT 'Y'
INTO v_check_flag
FROM ap_holds_all
WHERE invoice_id = x_invoice_id
AND release_lookup_code = 'HOLDS QUICK RELEASED';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_check_flag := 'N';
END;
DBMS_OUTPUT.put_line ('Hold Released (Y/N) : ' || v_check_flag);
END;
/
No comments:
Post a Comment