Wednesday, September 21, 2022
Wednesday, August 30, 2017
How to use DML Statements in a Function
We can not place any DML statement inside a function simply and
also a function with a DML statement cannot be used inside a SELECT
query.
Here is a small example supporting the above statement:
If we try to run the above function in a select query, the oracle engine throws the error as shown below:
But we can run the above DML statement based function in an anonymous block as shown below:
To use a DML statement inside a Function and also to use that function inside a SELECT query, we have to use PRAGMA AUTONOMOUS_TRANSACTION inside the function.
You can use the above function ‘fun2’ in a select query as shown below:
Here is a small example supporting the above statement:
CREATE OR REPLACE FUNCTION fun1(i_empno IN NUMBER) RETURN NUMBER AS i_count NUMBER; BEGIN -- DELETE FROM emp WHERE empno = i_empno; -- i_count:=sql%rowcount; -- RETURN i_count; END; / |
SQL> SELECT fun1(1) FROM dual; ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SYSTEM.FUN1", line 6 14551. 00000 - "cannot perform a DML operation inside a query " *Cause: DML operation LIKE INSERT, UPDATE, DELETE OR select-for-UPDATE cannot be performed inside a query OR UNDER a PDML slave. *Action: Ensure that the offending DML operation IS NOT performed OR USE an autonomous TRANSACTION TO perform the DML operation WITHIN the query OR PDML slave. |
DECLARE a NUMBER; BEGIN a:=fun1(7369); dbms_output.put_line(a); END; / |
CREATE OR REPLACE FUNCTION fun2( i_empno IN NUMBER) RETURN NUMBER AS pragma autonomous_transaction; i_count NUMBER; BEGIN DELETE FROM emp WHERE empno=i_empno; i_count:=sql%rowcount; COMMIT; RETURN i_count; END; / |
Function can use Out/Inout Parameter
Can we use an OUT/INOUT parameter in a function?
Yes of course.
A Small example:
CREATE OR REPLACE FUNCTION TEST(A IN NUMBER, B IN OUT NUMBER,
C OUT NUMBER) RETURN NUMBER IS
D NUMBER(3);
BEGIN
B := B + A;
C := B;
D := A + B + C;
RETURN D;
END;
DECLARE
A1 NUMBER(3) := 10;
B1 NUMBER(3) := 20;
C1 NUMBER(3) := 30;
D1 NUMBER(3) := 40;
BEGIN
D1 := TEST(A1,B1,C1);
DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);
END;
Yes of course.
A Small example:
CREATE OR REPLACE FUNCTION TEST(A IN NUMBER, B IN OUT NUMBER,
C OUT NUMBER) RETURN NUMBER IS
D NUMBER(3);
BEGIN
B := B + A;
C := B;
D := A + B + C;
RETURN D;
END;
DECLARE
A1 NUMBER(3) := 10;
B1 NUMBER(3) := 20;
C1 NUMBER(3) := 30;
D1 NUMBER(3) := 40;
BEGIN
D1 := TEST(A1,B1,C1);
DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);
END;
Thursday, August 3, 2017
Query to get frieght lines in R12
select a.trx_number,a.trx_date,b.line_type,sum(B.EXTENDED_AMOUNT) from ra_customer_trx_all a,RA_CUSTOMER_TRX_LINES_ALL b
where
a.CUSTOMER_TRX_ID = b.CUSTOMER_TRX_ID
--and a.TRX_NUMBER ='1891978'
and a.trx_date like '%-%-17'
and b.LINE_TYPE = 'FREIGHT'
group by a.trx_number,a.trx_date,b.line_type;
select a.trx_number,a.trx_date,b.line_type,sum(B.EXTENDED_AMOUNT) from ra_customer_trx_all a,RA_CUSTOMER_TRX_LINES_ALL b
where
a.CUSTOMER_TRX_ID = b.CUSTOMER_TRX_ID
--and a.TRX_NUMBER ='1891978'
and a.trx_date like '%-%-17'
and b.LINE_TYPE = 'FREIGHT'
group by a.trx_number,a.trx_date,b.line_type;
Tuesday, July 25, 2017
As an oracle apps technical consultant, we often deal with Oracle Workflow issues.To debug these issues we found following queries to be of immense use:
SQL Query To Find Out All Workflow Items For A Given Item Type:
SELECT item_type,
item_key,
to_char(begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
to_char(end_date,
'DD-MON-RR HH24:MI:SS') end_date,
root_activity activity
FROM apps.wf_items
WHERE item_type = '&item_type'
AND end_date IS NULL
ORDER BY to_date(begin_date,
'DD-MON-YYYY hh24:mi:ss') DESC;
SQL Query To Find Out All Notifications Sent By A Particular Workflow:
select wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from wf_notifications wn, wf_item_activity_statuses wias
where wn.group_id = wias.notification_id
and wias.item_type = '&item_type'
and wias.item_key = '&item_key';
/
SQL Query To Find the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find All Errored Workflow Activities For A Particular Item Type/ Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Errored Process Activity Statuses For A Particular Item Type/Item Key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find Out The Errored Activities For A Particular Item Type/Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
nvl(text_value,
nvl(to_char(number_value),
to_char(date_value))) VALUE
FROM wf_item_attribute_values
WHERE item_type = upper('&item_type')
AND item_key = nvl('&item_key',
item_key)
/
SQL Query To Find Out Number Of Deferred Workflow Activities:
SELECT COUNT(1),
was.item_type
FROM apps.wf_items wi,
apps.wf_item_activity_statuses was,
apps.wf_process_activities pra
WHERE wi.item_type = was.item_type
AND wi.item_key = was.item_key
AND wi.end_date IS NULL
AND was.end_date IS NULL
AND was.activity_status = 'DEFERRED'
--AND was.item_type = 'REQAPPRV'
AND was.item_type = wi.item_type
AND pra.instance_id(+) = was.process_activity
GROUP BY was.item_type;
SQL Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses
SELECT t.component_name,
p.owner,
p.queue_table,
t.correlation_id
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Find Records That Are Pending In Each Of The Workflow Agent Listener Queues;
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
nvl2(t.correlation_id,
'and corrid like ''' || t.correlation_id || ''' ',
NULL) || 'having count(*)>0;'
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Track The Status Of Notifications/Business Events That Are Waiting To Be Processed/That Have Errored Out:
SELECT a.user_data.geteventname(),
decode(a.state,
0,
'0 = Ready',
1,
'1 = Delayed',
2,
'2 = Retained/Processed',
3,
'3 = Exception',
to_char(a.state)) state,
a.user_data.PARAMETER_LIST,
a.user_data.event_data,
a.user_data.event_key,
a.*
FROM apps.wf_deferred a
WHERE corrid LIKE '%oracle.apps.wsh.sup.ssro'
AND rownum < 10;
'DD-MON-RR HH24:MI:SS') begin_date,
to_char(end_date,
'DD-MON-RR HH24:MI:SS') end_date,
root_activity activity
FROM apps.wf_items
WHERE item_type = '&item_type'
AND end_date IS NULL
ORDER BY to_date(begin_date,
'DD-MON-YYYY hh24:mi:ss') DESC;
SQL Query To Find Out All Notifications Sent By A Particular Workflow:
select wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from wf_notifications wn, wf_item_activity_statuses wias
where wn.group_id = wias.notification_id
and wias.item_type = '&item_type'
and wias.item_key = '&item_key';
/
SQL Query To Find the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find All Errored Workflow Activities For A Particular Item Type/ Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Errored Process Activity Statuses For A Particular Item Type/Item Key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find Out The Errored Activities For A Particular Item Type/Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
nvl(text_value,
nvl(to_char(number_value),
to_char(date_value))) VALUE
FROM wf_item_attribute_values
WHERE item_type = upper('&item_type')
AND item_key = nvl('&item_key',
item_key)
/
SQL Query To Find Out Number Of Deferred Workflow Activities:
SELECT COUNT(1),
was.item_type
FROM apps.wf_items wi,
apps.wf_item_activity_statuses was,
apps.wf_process_activities pra
WHERE wi.item_type = was.item_type
AND wi.item_key = was.item_key
AND wi.end_date IS NULL
AND was.end_date IS NULL
AND was.activity_status = 'DEFERRED'
--AND was.item_type = 'REQAPPRV'
AND was.item_type = wi.item_type
AND pra.instance_id(+) = was.process_activity
GROUP BY was.item_type;
SQL Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses
SELECT t.component_name,
p.owner,
p.queue_table,
t.correlation_id
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Find Records That Are Pending In Each Of The Workflow Agent Listener Queues;
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
nvl2(t.correlation_id,
'and corrid like ''' || t.correlation_id || ''' ',
NULL) || 'having count(*)>0;'
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Track The Status Of Notifications/Business Events That Are Waiting To Be Processed/That Have Errored Out:
SELECT a.user_data.geteventname(),
decode(a.state,
0,
'0 = Ready',
1,
'1 = Delayed',
2,
'2 = Retained/Processed',
3,
'3 = Exception',
to_char(a.state)) state,
a.user_data.PARAMETER_LIST,
a.user_data.event_data,
a.user_data.event_key,
a.*
FROM apps.wf_deferred a
WHERE corrid LIKE '%oracle.apps.wsh.sup.ssro'
AND rownum < 10;
Saturday, July 22, 2017
How to generate the XML Debug Logs
How to generate the
XML Debug Logs (Output Post Processor) for 'Payment Register'.
How to generate the
XML Debug Logs (Output Post Processor) for 'Payment Register'.
1. Create the temp directory in $XDO_TOP
On the applications tier move to $XDO_TOP
- create a temp directory - mkdir temp
- set the permissions - chmod 777 temp
- Use pwd and copy this directory location - it will be used in step 2
[applmgr@hostname ~]$ cd $XDO_TOP
[applmgr@hostname 12.0.0]$ mkdir temp
2. Create the Debug Config file
On the Applications Tier - change to $OA_JRE_TOP/bin - cd $OA_JRE_TOP/bin
- Execute this command - ./java -verbose | grep jre | grep 'java.lang.Runtime '
-In the results - the complete path is present showing where the rt.jar resides
- change to this directory - this is where the xdedebug.cfg should be created
- Use vi to create a new file called xdodebug.cfg
Enter these two lines:
LogLevel=STATEMENT
LogDir=complete_path_to_$XDO_TOP/temp directory you created
[applmgr@hostname 12.0.0]$ cd $OA_JRE_TOP/bin
[applmgr@hostname bin]$ ./java -verbose | grep jre | grep 'java.lang.Runtime '
[Loaded java.lang.Runtime from /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar]
[applmgr@hostname bin]$ cd /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ cat xdodebug.cfg
LogLevel=STATEMENT
LogDir=/app/apps/apps_st/appl/xdo/12.0.0/temp
[applmgr@hostname lib]$
3. Restart the Middle tier
cd $INST_TOP/admin/scripts
./adstopall.sh apps/apps
./adstrtall.sh apps/apps
4. Engage PO Output for Communication - using standard style sheet
Purchasing - Reports/Run
- Pass in the parameters to print the purchase order
- Leave the Debug parameter as the default
- Submit the request
5. Retrieve the logs
Move to $XDO_TOP/temp
- Here there are files crearted, please collect these and put into a zip file.
1. Create the temp directory in $XDO_TOP
On the applications tier move to $XDO_TOP
- create a temp directory - mkdir temp
- set the permissions - chmod 777 temp
- Use pwd and copy this directory location - it will be used in step 2
[applmgr@hostname ~]$ cd $XDO_TOP
[applmgr@hostname 12.0.0]$ mkdir temp
2. Create the Debug Config file
On the Applications Tier - change to $OA_JRE_TOP/bin - cd $OA_JRE_TOP/bin
- Execute this command - ./java -verbose | grep jre | grep 'java.lang.Runtime '
-In the results - the complete path is present showing where the rt.jar resides
- change to this directory - this is where the xdedebug.cfg should be created
- Use vi to create a new file called xdodebug.cfg
Enter these two lines:
LogLevel=STATEMENT
LogDir=complete_path_to_$XDO_TOP/temp directory you created
[applmgr@hostname 12.0.0]$ cd $OA_JRE_TOP/bin
[applmgr@hostname bin]$ ./java -verbose | grep jre | grep 'java.lang.Runtime '
[Loaded java.lang.Runtime from /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar]
[applmgr@hostname bin]$ cd /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ cat xdodebug.cfg
LogLevel=STATEMENT
LogDir=/app/apps/apps_st/appl/xdo/12.0.0/temp
[applmgr@hostname lib]$
3. Restart the Middle tier
cd $INST_TOP/admin/scripts
./adstopall.sh apps/apps
./adstrtall.sh apps/apps
4. Engage PO Output for Communication - using standard style sheet
Purchasing - Reports/Run
- Pass in the parameters to print the purchase order
- Leave the Debug parameter as the default
- Submit the request
5. Retrieve the logs
Move to $XDO_TOP/temp
- Here there are files crearted, please collect these and put into a zip file.
Enabling XDO logging
in EBS in 12.1.3
Introduction:
Some times ORACLE support team asks to get XDO logs to trouble
sheet problems related to XML Publisher reports or where XDO processing is
used.
Follow below steps to enable XDO logging
Steps:
a. Connect to the Apache server as applmgr
b. Create an $XDO_TOP/temp and an $XDO_TOP/resource directory
c. Create an xdodebug.cfg file in the $XDO_TOP/resource
directory (do not re-create if the file already exists), containing the
following 2 lines:
LogLevel=STATEMENT
LogDir=[full XDO_TOP]/temp
d. Restart the Apache server.
Note full full XDO_TOP means complete path
Note: The xdodebug.cfg file can also be created in the
$OA_JRE_TOP/jre/lib directory.
Thursday, July 6, 2017
Querry to Find Depended objects
Querry to Find Depended objects
SELECT "NAME", "OWNER", "TYPE", "OBJECT_ID", "STATUS", "TYPE_LINK" FROM(
SELECT DISTINCT
'SQLDEV:LINK:'||b.owner||':'||b.object_type||':'/*||lpad(' ',4*l)*/||b.object_name||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' as NAME,
b.owner,
b.object_type TYPE,
B.OBJECT_ID,
b.status,
replace(b.object_type,' ','_') type_link,
b.owner sdev_link_owner,
b.object_name sdev_link_name,
b.object_type sdev_link_type
FROM
sys.all_objects b,
(SELECT object_id, referenced_object_id, level l, rownum ord
FROM public_dependency
START WITH object_id = :OBJECT_ID
CONNECT BY NOCYCLE PRIOR referenced_object_id = object_id) c
WHERE b.object_id = c.referenced_object_id
AND b.owner NOT IN ('SYS', 'SYSTEM')
AND b.object_name <> 'DUAL'
--order by ord
);
Tuesday, June 27, 2017
RELEASED_STATUS in WSH_DELIVERY_DETAILS
RELEASED_STATUS in WSH_DELIVERY_DETAILS
Table: WSH_DELIVERY_DETAILS
Column: RELEASED_STATUS
Possible Values:
B: Backordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory
Monday, May 15, 2017
Difference between interface and conversion in oracle apps
legacy system:
****************
System other than oracle is legacy system.
Ex:Spread sheet.
Interface:
***********
1)It is a program to transfer the data from one location to another location.
2)Interface is used to transfer the data from legacy system to oracle system.
Interfaces are 2 types
1)Inbound interface: These interfaces are used to transfer the data from external system to oracle applications.
2)outbound interface:These interfaces are used to transfer the data from oracle applications to external systems.
Conversion:
************
Conversion is to bring the data from other(non-oracle apps) system to oracle application system.
Conversion is receiving the data from legacy system.
difference between interface and conversion in oracle apps
**************************************************************
INTERFACE:-
***********
IT IS A PROGRAM TO TRANSFER THE DATA FROM ONE LOCATION TO ANOTHER
LOCATION.
-->Interface is post production which is performed once before process.
-->interface is the integration of only(one way process)two systems.
-->interface is periodical process...
->IT IS SCHEDULED CONCURRENT PROCESS TO EXECUTED MULTIPLE TIMES
->WE WILL NOT BE KNOWING FLAT FILE VOLUME
->WE NEED TO HANDLE ALL EXPECTED EXCEPTIONS
->USED IN ENHANCEMENT,CUSTOMIZATION PROJECTS.
CONVERSION :-
****************
IT IS A PROGRAM
->IT IS ONE TIME DATA TRANSFER
-->it is one-time process.
-->data comes into oracle applications.
->WE WILL BE KNOWING THE VOLUME OF THE FLAT FILE
->NO NEED KNOW ALL EXCEPTIONS
->THIS IS USED IN UPGRADING,MIGRATION PROJECTS.
1)Open Interface: If the interface logic is provided by oracle applications,it is called Open interface.
******************
2)Custom Interface: If the interface logic is need to be developed by the implementation team, it is called Custom Interface.
********************
****************
System other than oracle is legacy system.
Ex:Spread sheet.
Interface:
***********
1)It is a program to transfer the data from one location to another location.
2)Interface is used to transfer the data from legacy system to oracle system.
Interfaces are 2 types
1)Inbound interface: These interfaces are used to transfer the data from external system to oracle applications.
2)outbound interface:These interfaces are used to transfer the data from oracle applications to external systems.
Conversion:
************
Conversion is to bring the data from other(non-oracle apps) system to oracle application system.
Conversion is receiving the data from legacy system.
difference between interface and conversion in oracle apps
**************************************************************
INTERFACE:-
***********
IT IS A PROGRAM TO TRANSFER THE DATA FROM ONE LOCATION TO ANOTHER
LOCATION.
-->Interface is post production which is performed once before process.
-->interface is the integration of only(one way process)two systems.
-->interface is periodical process...
->IT IS SCHEDULED CONCURRENT PROCESS TO EXECUTED MULTIPLE TIMES
->WE WILL NOT BE KNOWING FLAT FILE VOLUME
->WE NEED TO HANDLE ALL EXPECTED EXCEPTIONS
->USED IN ENHANCEMENT,CUSTOMIZATION PROJECTS.
CONVERSION :-
****************
IT IS A PROGRAM
->IT IS ONE TIME DATA TRANSFER
-->it is one-time process.
-->data comes into oracle applications.
->WE WILL BE KNOWING THE VOLUME OF THE FLAT FILE
->NO NEED KNOW ALL EXCEPTIONS
->THIS IS USED IN UPGRADING,MIGRATION PROJECTS.
1)Open Interface: If the interface logic is provided by oracle applications,it is called Open interface.
******************
2)Custom Interface: If the interface logic is need to be developed by the implementation team, it is called Custom Interface.
********************
Wednesday, May 10, 2017
ORA-01861: literal does not match format string
For any Concurrent Program date parameter in Oracle Apps, we assign FND_STANDARD_DATE Value Set having length 11. This value set always pass date in below format to your program
YYYY/MM/DD
If the date format of your program (Report or PL/SQL Procedure) does not matches with above format, it will always throw below error in log file
ORA-01861: literal does not match format string
Below are the steps to resolve this error with different Program Type
Oracle Reports Type Program
Define user parameter for Date in RDF with below property details
Datatype- Date
Width- 20
Input Mask- RRRR/MM/DD HH24:MI:SS
PL/SQL Stored Procedure Type Program
1) Always use errbuf and retcode as first two OUT parameters in your procedure
2) Define the Date parameters with datatype VARCHAR2 in procedure
3) Use fnd_date.canonical_to_date to convert varchar2 format to oracle date format (DD-MON-YY) and then use it anywhere in a program
Below is an example for the same
CREATE OR REPLACE PROCEDURE APPS.TEST_TRANSFER
( p_errbuf OUT VARCHAR2
,p_retcode OUT VARCHAR2
,p_from_date VARCHAR2
,p_to_date VARCHAR2 )
IS
v_from_date DATE;
v_to_date DATE;
BEGIN
v_from_date := fnd_date.canonical_to_date (p_from_date);
v_to_date := fnd_date.canonical_to_date (p_to_date);
Subscribe to:
Posts (Atom)