TechSpeak: August 2025

18 August, 2025

Script to unapply the invoice on Credit Memo - Oracle EBS

set serveroutput on;
declare
l_api_version CONSTANT NUMBER:=1;
l_init_msg_list CONSTANT VARCHAR2(1):=FND_API.g_false;
l_commit CONSTANT VARCHAR2(1):=FND_API.g_false;
l_acctd_amount_applied_from ar_receivable_applications_all.acctd_amount_applied_from%TYPE;
l_acctd_amount_applied_to ar_receivable_applications_all.acctd_amount_applied_to%TYPE;
l_cm_app_rec AR_CM_API_PUB.cm_app_rec_type;
v_cm_unapp_rec AR_CM_API_PUB.CM_UNAPP_REC_TYPE;
g_request_id NUMBER:=Fnd_Global.conc_request_id;
l_msg_count NUMBER;
l_msg_data VARCHAR2(2500);
v_msg_data VARCHAR2(4000);
v_process_status VARCHAR2(2500);
l_out_rec_application_id NUMBER;
-- v_process VARCHAR2(1):='Y';
l_return_status VARCHAR2(1);
l_gl_status VARCHAR2(50):=NULL;
v_accrual_cnt NUMBER:=0;
v_accrual_cm_cnt NUMBER:=0;
v_apply_amt NUMBER:=0;
v_accu_amt NUMBER:=0;
v_acm_amt NUMBER:=0;
v_request_id NUMBER;
l_boolean BOOLEAN;
v_xml_layout BOOLEAN;
l_phase VARCHAR2(200);
l_dev_phase VARCHAR2(200);
l_dev_status VARCHAR2(200);
l_message VARCHAR2(4000):=NULL;
l_status VARCHAR2(200);
v_print_report NUMBER;
v_rec_present NUMBER :=0;
g_gl_date DATE;

CURSOR get_inv_cur IS
<write your cursor to get details for invoice >

CURSOR get_accrualCM_cur IS

<write your cursor to get details for invoice >

BEGIN
dbms_output.put_line('-------------- Process Begins -------------------');
v_print_report := 0;


Mo_global.init('AR');
Mo_global.set_policy_context('S',23424); --23424
Fnd_Global.apps_initialize( 158939,59346,222); -- patch

FOR rec_inv IN get_inv_cur LOOP
/* Fnd_Global.apps_initialize( 157894, 52189, 222 );*/
v_process_status :=NULL;
v_msg_data :=NULL;
dbms_output.put_line('running for Actual Invoice : '||rec_inv.inv_trx_num);

FOR rec_acc_cm IN get_CM_cur(
rec_inv.inv_trx_num,
rec_inv.cust_acct_id
)LOOP
v_cm_unapp_rec.cm_customer_trx_id := rec_acc_cm.customer_trx_id;
v_cm_unapp_rec.inv_customer_trx_id := rec_acc.customer_trx_id;
v_cm_unapp_rec.reversal_gl_date := rec_acc_cm.gl_date;

/* --API Variables
l_cm_app_rec.cm_customer_trx_id :=rec_cm.customer_trx_id;
l_cm_app_rec.cm_trx_number :=rec_cm.CM_trx_num; -- Credit Memo Number
l_cm_app_rec.inv_customer_trx_id :=rec_inv.customer_trx_id;
l_cm_app_rec.inv_trx_number :=rec_inv.accrual_trx_num; -- Invoice Number
l_cm_app_rec.installment :=NULL;
l_cm_app_rec.applied_payment_schedule_id :=NULL;
l_cm_app_rec.amount_applied :=v_apply_amt;
l_cm_app_rec.apply_date :=g_gl_date;
l_cm_app_rec.gl_date :=g_gl_date ;--rec_cm.gl_date;
l_cm_app_rec.show_closed_invoices :='Y';
l_cm_app_rec.comments :='Showtime: Applied Accrual CM to accrual invoice ' ;
l_cm_app_rec.called_from :=NULL;

--Calling API
ar_cm_api_pub.apply_on_account(
p_api_version=>l_api_version,
p_init_msg_list=>l_init_msg_list,
p_commit=>l_commit,
p_cm_app_rec=>l_cm_app_rec,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
x_out_rec_application_id=>l_out_rec_application_id,
x_acctd_amount_applied_from=>l_acctd_amount_applied_from,
x_acctd_amount_applied_to=>l_acctd_amount_applied_to
);
*/
ar_cm_api_pub.unapply_on_account(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_cm_unapp_rec => v_cm_unapp_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);

COMMIT;
v_msg_data := l_msg_data;
IF NVL(l_return_status,'****')='S' THEN
dbms_output.put_line('Success for : '||rec_cm.CM_trx_num ||' Amt Applied: ' ||v_apply_amt);
v_process_status :='SUCCESS';
v_msg_data :=NULL;
v_print_report := v_print_report +1;
ELSE
IF l_msg_count=1 THEN
dbms_output.put_line(v_msg_data);
ELSIF l_msg_count>1 THEN
FOR i IN 1..l_msg_count LOOP
v_msg_data:=v_msg_data
||substr(
fnd_msg_pub.get(p_encoded=>fnd_api.g_false),
1,
1500
);
END LOOP;
dbms_output.put_line(v_msg_data);
END IF;
v_process_status := 'ERROR: '||v_msg_data;
dbms_output.put_line('Failure for: '||rec_cm.CM_trx_num || ' error msg: '|| v_msg_data);
COMMIT;
END IF; -- l_return_status = 'S' condition
END LOOP; -- cursor for CM loop
END LOOP;-- cursor for inv loop
dbms_output.put_line('-------------- Process Completed -------------------');

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error for CM '||SQLERRM);
END ;

Query to get TCS DFF Information - Oracle EBS

SELECT
ffv.descriptive_flexfield_name "DFF Name",
ffv.application_table_name "Table Name",
ffv.title "Title",
ap.application_name "Application",
ffc.descriptive_flex_context_code "Context Code",
ffc.descriptive_flex_context_name "Context Name",
ffc.description "Context Desc",
ffc.enabled_flag "Context Enable Flag",
att.column_seq_num "Segment Number",
att.form_left_prompt "Segment Name",
att.application_column_name "Column",
fvs.flex_value_set_name "Value Set",
att.display_flag "Displayed",
att.enabled_flag "Enabled",
att.required_flag "Required"
FROM
apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap
WHERE
ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id = ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code = att.descriptive_flex_context_code
AND fvs.flex_value_set_id (+) = att.flex_value_set_id
AND ffv.title IN ( 'Customer Information', 'Party Site Information', 'Party Information', 'Address Information', 'Site Use Information'
)
--AND att.form_left_prompt LIKE 'give TITLE NAME'
ORDER BY
ffv.title,
ffc.descriptive_flex_context_code,
att.column_seq_num

Assign responsibility to user based on program responsibility

DECLARE
lv_user_name VARCHAR2 (20) := 'XXUSER';
--lv_req_resp_key VARCHAR2 (50) := 'APPLICATION_DEVELOPER';
lv_description VARCHAR2 (100) := 'Adding Responsibility to user for report testing';
lv_req_resp_name VARCHAR2 (200);
lv_appl_shrt_name VARCHAR2 (20);
lv_appl_name VARCHAR2 (50);
lv_resp_key VARCHAR2 (50);

BEGIN

for c_cur in (

Select RESPONSIBILITY_KEY lv_req_resp_key,
(select application_short_name from fnd_application_vl fav where fr.application_id = fav.application_id) lv_appl_shrt_name
From apps.fnd_Responsibility fr,
apps.fnd_responsibility_tl frt,
apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs_tl fcpt
Where upper(fcpt.user_concurrent_program_name) like upper('%Aged Trial Balance with Payments%')
and request_group_name = 'XX_AR_INQ_REPORTS'
and fcpt.concurrent_program_id = frgu.request_unit_id
and frgu.request_group_id = frg.request_group_id
and frg.request_group_id = fr.request_group_id
and fr.responsibility_id = frt.responsibility_id
Order By 2
)

loop
fnd_user_pkg.addresp (username => lv_user_name,
resp_app => c_cur.lv_appl_shrt_name,
resp_key => c_cur.lv_req_resp_key,
security_group => 'STANDARD',
description => lv_description,
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
end loop;
DBMS_OUTPUT.put_line ('The responsibility ' || lv_req_resp_name || ' is added to the user ' || lv_user_name);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));
ROLLBACK;
END;

Query to get all details of the concurrent program

SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name" ,
fat.application_name ,
fl.meaning execution_method ,
fe.execution_file_name ,
fcp.output_file_type ,
fdfcuv.column_seq_num "Column Seq Number" ,
fdfcuv.end_user_column_name "Parameter Name" ,
fdfcuv.form_left_prompt "Prompt" ,
fdfcuv.enabled_flag " Enabled Flag" ,
fdfcuv.required_flag "Required Flag" ,
fdfcuv.display_flag "Display Flag" ,
fdfcuv.flex_value_set_id "Value Set Id" ,
ffvs.flex_value_set_name "Value Set Name" ,
flv.meaning "Default Type" ,
fdfcuv.default_value "Default Value"
FROM apps.fnd_concurrent_programs fcp ,
apps.fnd_concurrent_programs_tl fcpl ,
apps.fnd_descr_flex_col_usage_vl fdfcuv,
apps.fnd_flex_value_sets ffvs ,
apps.fnd_lookup_values flv ,
apps.fnd_lookups fl ,
apps.fnd_executables fe ,
apps.fnd_executables_tl fet ,
apps.fnd_application_tl fat
WHERE 1 = 1
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcp.enabled_flag = 'Y'
AND fcpl.user_concurrent_program_name = 'XX Vendor Invoice Payment Report' --<Your Concurrent Program Name>
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.language = 'US'
AND flv.language(+) = 'US'
AND fl.lookup_type ='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code =fcp.execution_method_code
AND fe.executable_id = fcp.executable_id
AND fe.executable_id =fet.executable_id
AND fet.language = 'US'
AND fat.application_id =fcp.application_id
AND fat.language = 'US'
ORDER BY fdfcuv.column_seq_num;

Query to get responsibility name based on menu name

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US'
--and responsibility_name ='AR Customer Maintenance'
and upper(user_menu_name) = 'XX_AR_CUST';

Query to get responsibility name based on function name - oracle EBS

SELECT distinct fr.responsibility_name,
fm.user_menu_name,
fme.prompt prompt_name,
fff.user_function_name
FROM apps.fnd_menu_entries_vl fme,
apps.fnd_menus_vl fm,
apps.fnd_form_functions_vl fff,
apps.fnd_form_vl ff,
apps.fnd_responsibility_vl fr
WHERE 1 = 1
AND fff.USER_FUNCTION_NAME = 'XX_CUST_CONTACT_FUN'
AND fme.function_id = fff.function_id
AND fm.menu_id = fme.menu_id
AND fr.menu_id = fm.menu_id;

Query to get ORG_ID based on responsibility Name

SELECT frv.responsibility_name,
fpov.profile_option_value org_id,
hou.name
FROM apps.fnd_profile_options_vl fpo,
apps.fnd_responsibility_vl frv,
apps.fnd_profile_option_values fpov,
apps.hr_organization_units hou
WHERE upper(frv.RESPONSIBILITY_NAME) like 'XX_RESP_NAME%'
AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = 'MO: Operating Unit'
AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name;

Query to get Reponsibilities attached to user

SELECT fu. user_name,
frv. responsibility_name,
frv. responsibility_key,
TO_CHAR (furgd. start_date, 'DD-MON-YYYY')"START_DATE",
TO_CHAR (furgd. end_date, 'DD-MON-YYYY') "END_DATE"
FROM apps.fnd_user fu,
apps.fnd_user_resp_groups_direct furgd,
apps. fnd_responsibility_vl frv
WHERE fu. user_id = furgd. user_id
AND furgd. responsibility_id = frv. responsibility_id
--AND furgd. end_date IS NULL
AND fu. user_name = 'TEST0101'
AND furgd. start_date <= sysdate AND NVL (furgd. end_date, sysdate + 1) > sysdate AND fu. start_date <= sysdate
AND NVL (fu. end_date, sysdate + 1) > sysdate
AND frv.start_date <= sysdate AND NVL (frv. end_date, sysdate + 1) > sysdate;
x

Query to get ORG_ID from Responsibility Name

SELECT fpov.profile_option_value, fr.*
FROM applsys.fnd_profile_option_values fpov
, applsys.fnd_profile_options fpo
, applsys.fnd_profile_options_tl fpot
, applsys.fnd_responsibility_tl fr
WHERE 1 = 1
AND fpo.profile_option_name = fpot.profile_option_name
AND fpo.profile_option_id = fpov.profile_option_id
AND fr.responsibility_id(+) = fpov.level_value
and fpot.language = 'US'
and fr.language = 'US'
AND fpot.profile_option_name = 'ORG_ID'
and fr.responsibility_name like '%XX%OM%Manager%';

Query to get the profile option details - Oracle EBS

SELECT fpo.profile_option_name SHORT_NAME,
fpot.user_profile_option_name NAME,
DECODE (fpov.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (fpov.level_id),
'10001', '',
'10002', fap.application_short_name,
'10003', frsp.responsibility_key,
'10005', fnod.node_name,
'10006', hou.name,
'10004', fu.user_name,
'UnDef')
"CONTEXT",
fpov.profile_option_value VALUE
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_user fu,
fnd_application fap,
fnd_responsibility frsp,
fnd_nodes fnod,
hr_operating_units hou
WHERE fpo.profile_option_id = fpov.profile_option_id(+)
AND fpo.profile_option_name = fpot.profile_option_name
AND fu.user_id(+) = fpov.level_value
AND frsp.application_id(+) = fpov.level_value_application_id
AND frsp.responsibility_id(+) = fpov.level_value
AND fap.application_id(+) = fpov.level_value
AND fnod.node_id(+) = fpov.level_value
AND hou.organization_id(+) = fpov.level_value
AND upper(fpot.user_profile_option_name) like '%DEFAULT%BUYER%'
ORDER BY short_name;

Script to delete concurrent program from backend - Oracle EBS

DECLARE
l_prog_short_name VARCHAR2 (240);
l_exec_short_name VARCHAR2 (240);
l_appl_full_name VARCHAR2 (240);
l_appl_short_name VARCHAR2 (240);
l_del_prog_flag VARCHAR2 (1) := 'Y'; --Set flag whether to delete Concurrent program or not
l_del_exec_flag VARCHAR2 (1) := 'Y'; --Set flag whether to delete executable or not
BEGIN
-- set concurrent program and executable short name
l_prog_short_name := 'XX_SHORT_NAME'; -- Concurrent program short name
l_exec_short_name := 'XX_SHORT_NAME'; -- Executable short name
l_appl_full_name := 'TEXT Custom Application'; -- Application full name
l_appl_short_name := 'TST_TOP'; -- Application Short name
-- Check if the program exists. if found, delete the program
IF fnd_program.program_exists (l_prog_short_name, l_appl_short_name)
AND fnd_program.executable_exists (l_exec_short_name, l_appl_short_name)
THEN
IF l_del_prog_flag = 'Y'
THEN
--API call to delete Concurrent Program
fnd_program.delete_program (l_prog_short_name, l_appl_full_name);
END IF;
IF l_del_exec_flag = 'Y'
THEN
--API call to delete Executable
fnd_program.delete_executable (l_exec_short_name, l_appl_full_name);
END IF;
COMMIT;
--
DBMS_OUTPUT.put_line ('Concurrent Program '||l_prog_short_name || ' deleted successfully');
DBMS_OUTPUT.put_line ('Executable '||l_exec_short_name || ' deleted successfully');
-- if the program does not exist in the system
ELSE
DBMS_OUTPUT.put_line (l_prog_short_name ||' or '||l_exec_short_name|| ' not found');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error while deleting: ' || SQLERRM);
END;

Delete XML Template and Data definition - Oracle EBS

SET SERVEROUTPUT ON

DECLARE
-- Change the following two parameters
var_templateCode VARCHAR2 (100) := 'XX_TEMPLATE_CODE'; -- Template Code
boo_deleteDataDef BOOLEAN := TRUE; -- delete the associated Data Def.
BEGIN
FOR RS
IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
T1.DATA_SOURCE_CODE,
T2.APPLICATION_SHORT_NAME DEF_APP_NAME
FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
WHERE T1.TEMPLATE_CODE = var_templateCode
AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
LOOP
XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

DELETE FROM XDO_LOBS
WHERE LOB_CODE = var_templateCode
AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND TEMPLATE_CODE = var_templateCode
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 50;

DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

IF boo_deleteDataDef
THEN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
RS.DATA_SOURCE_CODE);

DELETE FROM XDO_LOBS
WHERE LOB_CODE = RS.DATA_SOURCE_CODE
AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND LOB_TYPE IN
('XML_SCHEMA',
'DATA_TEMPLATE',
'XML_SAMPLE',
'BURSTING_FILE');

DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 30;

DBMS_OUTPUT.PUT_LINE (
'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE (
'Issue a COMMIT to make the changes or ROLLBACK to revert.');
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (
'Unable to delete XML Publisher Template ' || var_templateCode);
DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/

Validation Script For Migrated Objects - Oracle EBS

DBA objects :
select * from dba_objects where object_name like '%XX_TEST%';

Lookup :
SELECT * FROM apps.fnd_lookup_values
WHERE lookup_type in( 'XX_LOOKUP_NAME')
and enabled_flag = 'Y'
AND language = 'US';

Value set (with table validation) :
select *
from apps.fnd_flex_value_sets fs, apps.fnd_flex_validation_tables fv
where FLEX_VALUE_SET_NAME in ('XX_VALUESET_NAME')
-- and fv.FLEX_VALUE_ID = fvt.FLEX_VALUE_ID
-- and language = 'US'
and fv.FLEX_VALUE_SET_ID = fs.FLEX_VALUE_SET_ID
;

Value set values:
select fv.FLEX_VALUE, fvt.FLEX_VALUE_MEANING, fvt.DESCRIPTION
from apps.fnd_flex_value_sets fs,apps.fnd_flex_values fv ,
apps.fnd_flex_values_tl fvt
where FLEX_VALUE_SET_NAME in ('XX_VALUESET_NAME')
and fv.FLEX_VALUE_ID = fvt.FLEX_VALUE_ID
and language = 'US'
and fv.FLEX_VALUE_SET_ID = fs.FLEX_VALUE_SET_ID;

Executables:
select * from apps.fnd_executables where EXECUTABLE_NAME like 'XXEXE_NAME';

Concurrent programs :
select * from APPS.fnd_concurrent_programs
where CONCURRENT_PROGRAM_NAME in ('XX_CONC_PROG_NAME')
order by CONCURRENT_PROGRAM_ID desc;

Request group for conc prog :
Select fcpt.CONCURRENT_PROGRAM_NAME ,
frg.request_group_name
From
apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs fcpt
Where fcpt.concurrent_program_id = frgu.request_unit_id
and frgu.request_group_id = frg.request_group_id
and CONCURRENT_PROGRAM_NAME in ('XX_CONC_PROG_NAME')
Order By 2;

Conc. Programs parameters:
SELECT DISTINCT fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name" ,
fat.application_name ,
fl.meaning execution_method ,
fe.execution_file_name ,
fcp.output_file_type ,
fdfcuv.column_seq_num "Column Seq Number" ,
fdfcuv.end_user_column_name "Parameter Name" ,
fdfcuv.form_left_prompt "Prompt" ,
fdfcuv.enabled_flag " Enabled Flag" ,
fdfcuv.required_flag "Required Flag" ,
fdfcuv.display_flag "Display Flag" ,
fdfcuv.flex_value_set_id "Value Set Id" ,
ffvs.flex_value_set_name "Value Set Name" ,
flv.meaning "Default Type" ,
fdfcuv.default_value "Default Value"
FROM apps.fnd_concurrent_programs fcp ,
apps.fnd_concurrent_programs_tl fcpl ,
apps.fnd_descr_flex_col_usage_vl fdfcuv,
apps.fnd_flex_value_sets ffvs ,
apps.fnd_lookup_values flv ,
apps.fnd_lookups fl ,
apps.fnd_executables fe ,
apps.fnd_executables_tl fet ,
apps.fnd_application_tl fat
WHERE 1 = 1
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcp.enabled_flag = 'Y'
AND CONCURRENT_PROGRAM_NAME in ('XX_CONC_PROG_NAME')
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.language = 'US'
AND flv.language(+) = 'US'
AND fl.lookup_type ='CP_EXECUTION_METHOD_CODE'
AND fl.lookup_code =fcp.execution_method_code
AND fe.executable_id = fcp.executable_id
AND fe.executable_id =fet.executable_id
AND fet.language = 'US'
AND fat.application_id =fcp.application_id
AND fat.language = 'US'
ORDER BY CONCURRENT_PROGRAM_NAME;

Executables :
select * from APPS.fnd_executables where EXECUTABLE_NAME in ('XX_EXE_NAME');

Templates :
select * from XDO.xdo_templates_tl where TEMPLATE_CODE = 'XX_TEMPLATE_CODE';

Data definition :
select * from XDO.xdo_ds_definitions_tl where DATA_SOURCE_CODE = 'XX_CODE' ;

XDO attachments :
select * from apps.XDO_LOBS where file_name like 'XX_FILE_NAME%';

Form function :
select * from apps.fnd_form_functions_vl where USER_FUNCTION_NAME = 'XX_FORM_FUN' ;

Customer Relation Query - Oracle EBS

SELECT hcr.cust_account_id,
hcr.related_cust_account_id,
hcr.relationship_type,
hcr.customer_reciprocal_flag,
hcr.status,
hcr.org_id,
hcr.bill_to_flag,
hcr.ship_to_flag,
hca.account_name AS rel_act_desc,
hp.party_name AS rel_customer,
hca.account_number AS rel_act_number,
hp.party_id
FROM apps.hz_cust_acct_relate_all hcr,
apps.hz_cust_accounts_all hca,
apps.hz_parties hp
WHERE 1=1
AND hca.cust_account_id = hcr.related_cust_account_id
AND hca.party_id = hp.party_id
-- and hp.party_number=:PARTY_NUMBER
and hca.cust_account_id = 213139 ;

LDT Commands Oracle EBS

Concurrent program download :

==============================

FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_DAILY_PAYMENT_REPORT_XL.ldt PROGRAM APPLICATION_SHORT_NAME="XXTOP" CONCURRENT_PROGRAM_NAME="XX_DAILY_PAYMENT_REPORT_XL"

Data definition and template download :

======================================

FNDLOAD apps/password O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_DAILY_PAYMENT_REPORT_XL_DDTEMP.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXTOP' DATA_SOURCE_CODE='XX_DAILY_PAYMENT_REPORT_XL' TMPL_APP_SHORT_NAME='XXTOP' TEMPLATE_CODE='XX_DAILY_PAYMENT_REPORT_XL'

Concurrent program upload:

=============================

FNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_DAILY_PAYMENT_REPORT_XL.ldt

Data definition and template upload:

======================================

FNDLOAD apps/password 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_DAILY_PAYMENT_REPORT_XL_DDTEMP.ldt

Script to run Workflow background process - Oracle EBS

DECLARE
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_user_id NUMBER;
l_request_id NUMBER;
BEGIN

SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_responsibility_id, l_application_id
FROM fnd_responsibility frx, fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND fr.responsibility_name LIKE 'System Administrator';

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'TEST_USER';

--To set environment context.

fnd_global.apps_initialize (l_user_id
,l_responsibility_id
,l_application_id );

--Submitting Concurrent Request

l_request_id :=
fnd_request.submit_request (application => 'FND',
program => 'FNDWFBG',
description => 'Workflow background process for deferred and timeout activities',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => NULL,
argument2 => NULL,
argument3 => NULL,
argument4 =>'YES',
argument5 =>'YES',
argument6 =>'YES'
);
--
COMMIT;

IF l_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Concurrent request
failed to submit');
ELSE
DBMS_OUTPUT.put_line ('Successfully
Submitted the Concurrent Request'|| l_request_id);
END IF;
--
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Error While Submitting
Concurrent Request '
|| TO_CHAR (SQLCODE)
|| '-'
|| SQLERRM
);
END;

/

Oracle EBS AR Customer Profile creation API

SET SERVEROUTPUT ON;
DECLARE
p_customer_profile_rec_type HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
x_cust_account_profile_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
cursor c_cust is select cust_account_id
from hz_cust_accounts
where account_number in ('TEST110887','TEST518421');
BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id => 20276
,resp_id => 51117
,resp_appl_id => 222);
mo_global.set_policy_context('S',101);
fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters
for r_cust in c_cust loop
p_customer_profile_rec_type.cust_account_id := r_cust.cust_account_id;
p_customer_profile_rec_type.created_by_module := 'CUST_INTERFACE';
p_customer_profile_rec_type.profile_class_id := 1040 ; -- give the profile class id
--p_customer_profile_rec_type.customer_profile_class_name := 'TEST_DEFAULT';
p_customer_profile_rec_type.credit_hold := 'N';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_customer_profile_v2pub.create_customer_profile');
HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE
(
p_init_msg_list => FND_API.G_TRUE,
p_customer_profile_rec => p_customer_profile_rec_type,
p_create_profile_amt => FND_API.G_TRUE,
x_cust_account_profile_id => x_cust_account_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Customer Profile is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
dbms_output.put_line('Cust Account Id = '||TO_CHAR(p_customer_profile_rec_type.cust_account_id));
dbms_output.put_line('Cust Account Profile Id = '||TO_CHAR(x_cust_account_profile_id));
dbms_output.put_line('Status = '||p_customer_profile_rec_type.status);
dbms_output.put_line('Credit Checking = '||p_customer_profile_rec_type.credit_checking);
dbms_output.put_line('Interest Charges = '||p_customer_profile_rec_type.interest_charges);
ELSE
DBMS_OUTPUT.put_line ('Creation of Customer Profile got failed:'||x_msg_data);
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
/

01 August, 2025

Query to get request group name from request set

SELECT frs.request_set_name,
--frs.description AS request_set_description,
frg.request_group_name,
frg.description AS request_group_description
FROM apps.fnd_request_sets frs
JOIN apps.fnd_request_group_units frgu
ON frs.request_set_id = frgu.request_unit_id
JOIN apps.fnd_request_groups frg
ON frgu.request_group_id = frg.request_group_id
WHERE frs.request_set_name = 'SAMPLE_REQ_SET_NAME';

Query to get responsibility list for a concurrent program

Select fcpt.user_concurrent_program_name ,
frg.request_group_name ,
frt.responsibility_name
From apps.fnd_Responsibility fr,
apps.fnd_responsibility_tl frt,
apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs_tl fcpt
Where upper(fcpt.user_concurrent_program_name) like upper('%GL Period%')
and fcpt.concurrent_program_id = frgu.request_unit_id
and frgu.request_group_id = frg.request_group_id
and frg.request_group_id = fr.request_group_id
and fr.responsibility_id = frt.responsibility_id
Order By 2

Sample Script to View EBS Frontend Password

SELECT usertable.user_name ,
(SELECT get_pwd.decrypt (UPPER (
(SELECT
(SELECT get_pwd.decrypt (UPPER (
(SELECT UPPER (FND_WEB_SEC.GET_GUEST_USERNAME_PWD) FROM DUAL
)), usertable.encrypted_foundation_password)
FROM DUAL
) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER (
(SELECT SUBSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD,1, INSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/')- 1)
FROM DUAL
))
)),usertable.encrypted_user_password)
FROM DUAL
) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('USER123');

Sample Script to reset EBS Frontend user password

Script to Reset User Password:

SET serveroutput ON;
DECLARE
v_user_name VARCHAR2(30):= UPPER('USER123');
v_new_password VARCHAR2(30):= 'Welcome1';
v_status BOOLEAN;
BEGIN
v_status := fnd_user_pkg.ChangePassword ( username => v_user_name,
newpassword => v_new_password
);
IF v_status =TRUE THEN
dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END IF;
END;

Sample Script API HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

SET SERVEROUTPUT ON;
DECLARE
p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
p_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
p_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
p_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
p_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
p_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_contact_point_id NUMBER;
BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id => 158240
,resp_id => 51117
,resp_appl_id => 222);
mo_global.set_policy_context('S',101);
-- Initializing the Mandatory API parameters
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
p_contact_point_rec.owner_table_id := 8787722;
p_contact_point_rec.primary_flag := 'Y';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.created_by_module := 'TCA_V2_API';
p_phone_rec.phone_area_code := NULL;
p_phone_rec.phone_country_code := '1';
p_phone_rec.phone_number := '856-784-521';
p_phone_rec.phone_line_type := 'MOBILE';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_contact_point_v2pub.create_contact_point');
HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT
(
p_init_msg_list => FND_API.G_TRUE,
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => p_edi_rec,
p_email_rec => p_email_rec,
p_phone_rec => p_phone_rec,
p_telex_rec => p_telex_rec,
p_web_rec => p_web_rec,
x_contact_point_id => x_contact_point_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Contact Point is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('x_contact_point_id = '||x_contact_point_id);

ELSE
DBMS_OUTPUT.put_line ('Creation of Contact Point got failed:'||x_msg_data);
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
/

Sample Script API Hz_Party_Contact_V2pub.create_org_contact

set serveroutput on;
declare
x_org_contact_id NUMBER;
x_party_rel_id NUMBER;
x_org_party_id NUMBER;
x_org_party_number VARCHAR2(2000);
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (20000);
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (20000) := NULL;
p_org_contact_rec Hz_Party_Contact_V2pub.org_contact_rec_type;
BEGIN
p_org_contact_rec.party_site_id := 8787727;
p_org_contact_rec.party_rel_rec.subject_id := 10572128;
p_org_contact_rec.created_by_module := 'TCA_V2_API';
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id := 10122562;
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.status := 'A';
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
apps.Fnd_Msg_Pub.delete_msg (NULL);
apps.Fnd_Msg_Pub.initialize ();
Hz_Party_Contact_V2pub.create_org_contact
(p_init_msg_list => Fnd_Api.g_true,
p_org_contact_rec => p_org_contact_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_org_contact_id => x_org_contact_id,
x_party_rel_id => x_party_rel_id,
x_party_id => x_org_party_id,
x_party_number => x_org_party_number
);
IF x_return_status != 'S'
THEN
l_error_message := NULL;

DBMS_OUTPUT.put_line ('Creation of Org Contact got failed:'||x_msg_data);

FOR i IN 1 .. x_msg_count
LOOP
apps.Fnd_Msg_Pub.get (p_msg_index => i,
p_encoded => Fnd_Api.g_false,
p_data => x_msg_data,
p_msg_index_out => l_msg_index_out
);
IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
DBMS_OUTPUT.put_line ('Error: '||l_error_message);
END LOOP;
else
DBMS_OUTPUT.PUT_LINE('Creation of Org Contat is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('x_org_contact_id = '||x_org_contact_id);
DBMS_OUTPUT.PUT_LINE('x_party_rel_id = '||x_party_rel_id);
DBMS_OUTPUT.PUT_LINE('x_org_party_id = '||x_org_party_id);
DBMS_OUTPUT.PUT_LINE('x_org_party_number = '||x_org_party_number);

END IF;
EXCEPTION
WHEN OTHERS
THEN
x_party_rel_id := NULL;
x_org_party_id := NULL;

END;

/*
Output information ....
x_org_contact_id = 5969901
x_party_rel_id = 6047470
x_org_party_id = 10572137
x_org_party_number = 4934314
*/

Sample Script API hz_cust_account_site_v2pub.create_cust_site_use

SET SERVEROUTPUT ON;
DECLARE
p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
x_site_use_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id => 158240
,resp_id => 51117
,resp_appl_id => 222);
mo_global.set_policy_context('S',101);
-- Initializing the Mandatory API parameters
p_cust_site_use_rec.cust_acct_site_id := 3670724;
p_cust_site_use_rec.site_use_code := 'BILL_TO';
p_cust_site_use_rec.location := '110974-30362';
p_cust_site_use_rec.created_by_module := 'CUST_INTERFACE';
p_cust_site_use_rec.primary_flag := 'Y';
p_cust_site_use_rec.orig_system_reference :='110974-30362';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_cust_account_site_v2pub.create_cust_site_use');
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE
(
p_init_msg_list => FND_API.G_TRUE,
p_cust_site_use_rec => p_cust_site_use_rec,
p_customer_profile_rec => p_customer_profile_rec,
p_create_profile => FND_API.G_TRUE,
p_create_profile_amt => FND_API.G_TRUE,
x_site_use_id => x_site_use_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Customer Accnt Site use is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('Site Use Id = '||x_site_use_id);
DBMS_OUTPUT.PUT_LINE('Site Use = '||p_cust_site_use_rec.site_use_code);
ELSE
DBMS_OUTPUT.put_line ('Creation of Customer Accnt Site use got failed:'||x_msg_data);
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
/