How to use DML Statements in a Function

You can use DML inside a PL/SQL function.However, the function can only be called from PL/SQL, not from SQL.

Generally you cannot do a dml in function and call the function in select.. The reason is "Select statement should not alter any data".. "SELECT" is not coming under DML.. but again by using Pragma Autonomous Transaction you can do.. The reason is when u use Pragma Autonomous Transaction, the DML Part will be executed in a Seperate Session (not from the Session where u issued select call).. but its never a god idea... Dont use DML in functions... Use Procedures

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:

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; /

If we try to run the above function in a select query, the oracle engine throws the error as shown below:

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.

But we can run the above DML statement based function in an anonymous block as shown below:

DECLARE a NUMBER; BEGIN a:=fun1(7369); dbms_output.put_line(a); END; /

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.

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; /

You can use the above function ‘fun2’ in a select query as shown below:

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; 

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;

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;  

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.

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.

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
);

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

http://www.geeksengine.com/article/oracle-cross-table-update.html

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.
********************

ORA-01861: literal does not match format string

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);