Wednesday, June 15, 2011
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;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
2 Responses to “API to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)”
June 27, 2011 at 9:48 PM
Hi,
Is this API ap_holds_pkg.release_single_hold a published API? How was it concluded that this must be the API that must be used (apart from the fact that this procedure updates the ap_holds_all table)? Would oracle support a update done via this API?
June 30, 2014 at 8:17 PM
Good and Useful document for a newbie.
Many Thanks
Post a Comment