Tuesday, January 2, 2018
How to verify the xml template file
How to find the oracle workflow version using sql query
Tuesday, October 31, 2017
Oracle Applications front end URL from Back end
FROM fnd_profile_option_values
WHERE profile_option_id=
(SELECT profile_option_id
FROM fnd_profile_options WHERE
profile_option_name = 'APPS_FRAMEWORK_AGENT')
AND level_value = 0;
SELECT home_url FROM icx_parameters;
Tuesday, August 22, 2017
Query to find current running SQL for given Concurrent Program
s.status,
s.username,
s.osuser,
a.sql_text,
s.SID
FROM v$sql a,
v$session s
WHERE s.sql_address = a.address
AND s.SID IN
(SELECT d.SID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
--v$sql e
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND d.paddr = c.addr
--AND d.sql_address = e.address
AND a.request_id = 10803707 (provide the actual request id)
and a.PHASE_CODE = 'R'
)
Query to find Indexed Column names in table
FROM user_indexes i, user_ind_columns c
WHERE i.index_name = c.index_name
AND i.table_name = UPPER(:P_TABLE_NAME)
ORDER BY c.index_name, c.column_position;
Tuesday, November 1, 2016
What is hold in OM ? What are the types of Holds in Order Management.
Sunday, September 20, 2015
How to progress Errored workflow using back end api
CURSOR c1
IS
SELECT c.INSTANCE_LABEL,a.line_id
FROM oe_order_lines_all a,
apps.wf_item_activity_statuses b,
apps.wf_process_activities c
WHERE header_id = 602953547
and b.item_key = to_char(a.line_id)
and b.item_type ='OEOL'
and b.activity_status='ERROR'
and b.process_activity = c.instance_id;
BEGIN
FOR c1_rec IN c1
LOOP
wf_engine.handleerror (
'OEOL',
TO_CHAR (c1_rec.line_id),
C1_REC.INSTANCE_LABEL,
'RETRY',
NULL
);
END LOOP;
commit;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
Wednesday, July 22, 2015
How to release the Hold automatically when it reaches the hold until date
Friday, March 13, 2015
How to raise business event manually using script
l_event_name VARCHAR2 (240)
:= 'oracle.apps.ont.genesis.outbound.update';
l_event_parameter_list wf_parameter_list_t := wf_parameter_list_t ();
BEGIN
wf_event.addparametertolist (
p_name=> 'HEADER_ID',
p_value=> '96567456745',
p_parameterlist=> l_event_parameter_list
);
wf_event.addparametertolist (
p_name=> 'LINE_ID',
p_value=> '865875768',
p_parameterlist=> l_event_parameter_list
);
wf_event.addparametertolist (
p_name=> 'HOLD_SOURCE_ID',
p_value=>'54326456',
p_parameterlist=> l_event_parameter_list
);
wf_event.addparametertolist (
p_name=> 'CHANGE_TYPE',
p_value=> 'APPLY',
p_parameterlist=> l_event_parameter_list
);
BEGIN
wf_event.RAISE (
p_event_name=> l_event_name,
p_event_key=> SYS_GUID (),
p_parameters=> l_event_parameter_list
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
' Unexpected Error : '
|| SUBSTR (SQLERRM, 1, 250)
);
END;
COMMIT;
l_event_parameter_list.DELETE;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
' Unexpected Error in main block : '
|| SUBSTR (SQLERRM, 1, 250)
);
END;
Thursday, January 8, 2015
Concurrent Request Status Codes
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
AND ENABLED_FLAG = 'Y'
AND VIEW_APPLICATION_ID = 0
ORDER BY LOOKUP_CODE;
STATUS_CODE | MEANING |
A | Waiting |
B | Resuming |
C | Normal |
D | Cancelled |
E | Error |
G | Warning |
H | On Hold |
I | Normal |
M | No Manager |
P | Scheduled |
Q | Standby |
R | Normal |
S | Suspended |
T | Terminating |
U | Disabled |
W | Paused |
X | Terminated |
Z | Waiting |
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
AND ENABLED_FLAG = 'Y'
AND VIEW_APPLICATION_ID = 0
ORDER BY PHASE_CODE;
STATUS_CODE | MEANING |
C | Completed |
I | Inactive |
P | Pending |
R | Running |
Monday, December 15, 2014
How to find the inbound and out bound mailer details
c.parameter_name,
a.parameter_value
FROM apps.fnd_svc_comp_param_vals a,
apps.fnd_svc_components b,
apps.fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('INBOUND_SERVER','OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;
Sunday, November 23, 2014
Concurrent Request Status and Phase Codes
Concurrent Request Status and Phase Codes
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
AND ENABLED_FLAG = 'Y'
AND VIEW_APPLICATION_ID = 0
ORDER BY LOOKUP_CODE;
STATUS_CODE | MEANING |
A | Waiting |
B | Resuming |
C | Normal |
D | Cancelled |
E | Error |
G | Warning |
H | On Hold |
I | Normal |
M | No Manager |
P | Scheduled |
Q | Standby |
R | Normal |
S | Suspended |
T | Terminating |
U | Disabled |
W | Paused |
X | Terminated |
Z | Waiting |
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
AND ENABLED_FLAG = 'Y'
AND VIEW_APPLICATION_ID = 0
ORDER BY PHASE_CODE;
STATUS_CODE | MEANING |
C | Completed |
I | Inactive |
P | Pending |
R | Running |
Tuesday, November 4, 2014
checking the Environment Variable from back end query
SELECT variable_name, VALUE FROM apps.fnd_env_context WHERE concurrent_process_id = (SELECT MAX(concurrent_process_id) FROM apps.fnd_env_context ) ORDER BY variable_name;
Wednesday, July 16, 2014
To Find Latest Query Executing/Running in Database for Specific Concurrent Request
Find Latest Query Executing/Running in Database for Specific Concurrent Request
If one concurrent request is running and you want to find currently which query of that request is running in session then you can run below query in toad or sql developer by putting request ifd of that concurrent request-This will be very useful for debugging purposes means we can derive what part of code is taking time or where program got stuck and for other purposes.
SQL Query to Find Scheduled Concurrent Programs in System
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
Then Substr(
fu.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
FROM fnd_concurrent_requests fcr
, fnd_user fu
, fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpt
, fnd_printer_styles_tl fpst
, fnd_conc_release_classes fcrc
, fnd_responsibility_tl frt
, fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;
WFLOAD – Oracle Workflow Loader utility
WFLOAD Command to download Oracle Workflow
Syntax-
WFLOAD <apps/pwd>@<connectstring> 0 Y DOWNLOAD FILE_NAME.wft item_type
Modes-
UPGRADE – Protection and Customization levels of data
UPLOAD - Only protection level of data.Not supporting customisation
FORCE - Force upload, protection or customization not supported
For example-
WFLOAD apps/apps@VIS 0 Y DOWNLOAD POAPPRV_V1.wft POAPPRV
WFLOAD Command to upload Oracle Workflow
Syntax-
WFLOAD <apps/pwd>@<connectstring> 0 Y {UPLOAD | UPGRADE | FORCE} FILE_NAME.wft
For Example-
WFLOAD apps/apps@VIS 0 Y UPLOAD POAPPRV_V1.wft
Seeded Workflow Files Location
You can also download seeded workflow definition files directly from below directory-
$<Application_TOP>/patch/115/import/<LANG>
For example- Account Payables workflow file can be found at-
$AP_TOP/patch/115/import/US
Wednesday, May 21, 2014
RESULT_CACHE hint on Oracle 11G
Monday, April 28, 2014
How to find all the valid hold release reason codes from back end
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = 'RELEASE_REASON'
and enabled_flag ='Y'
and end_date_active is null