Workflow Quick Ref

1. COMPILE ALL DEPENDENCIES:
===========================

DECLARE
v_referenciado varchar2(20):=' e.i. : Table1';
BEGIN
FOR c IN (
SELECT name,type,owner
FROM all_dependencies
WHERE referenced_name = v_referenciado
ORDER BY NAME
)
LOOP
dbms_ddl.alter_compile(c.type,c.owner,c.name);
END LOOP;
END ;


select name , type , referenced_name , referenced_type
from user_dependencies
where name = ‘PO_HEADERS_V’
and ( type = ‘VIEW’ or referenced_type = ‘TABLE’)



2. Apps Initialize
==================

select fnd.user_id ,
       fresp.responsibility_id,
       fresp.application_id
from   fnd_user fnd
,      fnd_responsibility_tl fresp
where  fnd.user_name = 'SXS29'
and    fresp.responsibility_name = 'Purchasing Super User';

begin
mo_global.set_policy_context('s',110);
end;

fnd_global.apps_initialize(2509,20634,401);


3. Change Application Password
===============================

http://imdjkoch.wordpress.com/2011/04/26/apis-to-create-userreset-password-and-add-responsibility/

DECLARE
   v_user_name      VARCHAR2 (100) := 'CONT-AXP20';
   v_new_password   VARCHAR2 (100) := 'oracle123';
   v_status         BOOLEAN        := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);
   COMMIT;
   DBMS_OUTPUT.put_line (   'Password is changed successfully for the user '
                         || v_user_name
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while setting new password to the user and the error is '
          || SQLERRM
         );
END;


4. Some Important Profiles
===========================

1. set Server Timezone - to open status monitor
2. Utilities Diagnastics - Yes - to work on form personalizations


5. workflow Queries/Tables:
===========================
The message attribute table contains a row for each message attribute listed in the WF_NOTIFICATION() call, showing the display name and the value for each attribute

WF_NOTIFICATION(HISTORY)

WF_NOTIFICATION(ATTRS,<attribute1>,<attribute2>,<attribute3>,...)

Note: WF_NOTIFICATION() is not a PL/SQL function, but rather a special message function that can only be called within an Oracle Workflow message body.
Note: You must not include any spaces or carriage returns in the call to WF_NOTIFICATION(). You only need to use a comma to delimit the parameters in the list.

The #HIDE_REASSIGN attribute must be either of type text or lookup.
================================
To hide the Reassign button in the Notification Details page, and to prevent reassignment from the Advanced Worklist, Personal Worklist, and self-service home page, as well as through vacation rules, set the value of this attribute to Y.
To hide the Reassign button in the Notification Details page, and to prevent reassignment from the Advanced Worklist, Personal Worklist, and self-service home page, but still allow reassignment through vacation rules, set the value of this attribute to B.
To display the Reassign button in the Notification Details page, and to allow reassignment from the Advanced Worklist, Personal Worklist, self-service home page, and vacation rules, set the value to N.

#HIDE_MOREINFO Attribute
=========================
If you always want to hide the Request Information button for notifications using a particular message, specify the value Y as a constant.

6) WFupload and DownLoad
=========================
WFLOAD apps/apps@arsdev2 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>]
WFLOAD apps/apps@arsdev3 100 Y FORCE REQAPPRV.wft

WFLOAD apps/appst3lp207@ARSPROD 0 Y DOWNLOAD REQAPPRV.wft REQAPPRV


7) How to Close the FYI notifications Automatically
==========================================================
1) Go to system administrator responsibility.
2) Click on Workflow Manager inside Workflow : Oracle Applications Manager.
3) Click on the button next to Notification Mailers.
4) Click on the Edit Button. Click on Advanced Button. Click on Next button 3 times.
5) Then you will see Autoclose FYI check box. Tick that box and Click on Finish Button twice.
6) Click on Apply Button.

This will automatically close all the FYI notifications.


8) To view all notifications from Workflow Admin
=================================================

UPDATE WF_RESOURCES
SET TEXT='FND_RESP|FND|FNDWF_ADMIN|STANDARD'
WHERE NAME = 'WF_ADMIN_ROLE';

COMMIT;


9) To monitor workflow from backend
===================================
SELECT *
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_TYPE='XXNCMMST'
--AND BEGIN_DATE>SYSDATE-0.01 
AND ITEM_KEY='QAACTION14004'


SELECT *
FROM WF_PROCESS_ACTIVITIES
WHERE INSTANCE_ID IN (SELECT PROCESS_ACTIVITY
                      FROM   WF_ITEM_ACTIVITY_STATUSES
                      WHERE  ITEM_TYPE='XXNCMMST' --AND BEGIN_DATE>SYSDATE-0.1 
                      and    item_key='QAACTION14004'
                     )

10) Query to get sequences of workflow process
==============================================
select process_item_type,process_name,
       (select instance_label from wf_process_activities where instance_id=from_process_activity) from_process_activity,
       result_code,
       (select instance_label from wf_process_activities where instance_id=to_process_activity) to_process_activity
from wf_activity_transitions wat,wf_process_activities wpa
where wpa.instance_id=wat.from_process_activity
and process_item_type='OEOL'
and process_name like 'XXARS%'



select * from wf_notifications where item_key =(select wf_item_key from po_requisition_headers_all  where segment1 ='125')


11). to get latest responder on the item key
============================================

         SELECT responder
           INTO l_responder
           FROM wf_notifications
          WHERE notification_id =
                          (SELECT MAX (notification_id)
                             FROM wf_notifications
                            WHERE item_key = l_item_key AND status = 'CLOSED' AND responder is not null);


           select *
           from wf_process_activities
           where process_item_type='REQAPPRV'
           and process_name='XXARS_MAIN_REQAPPRV_PROCESS'
           and instance_id in (select process_activity
                               from wf_item_activity_statuses
                               where item_type='REQAPPRV'
                               and item_key=(select wf_item_key from po_requisition_headers_all  where segment1 ='126'))


-- select * from oe_transaction_types_tl where transaction_type_id=(select line_type_id from oe_order_lines_all where line_id=5002) and language='US'
                           
select * from oe_transaction_types_tl where transaction_type_id=(select order_type_id from oe_order_headers_all where header_id=12009) and language='US'


12).Query to refresh mview
============================

begin
dbms_mview.refresh('mv name','F/C');
end;


13). Query to get responsibility names if u know request id of a concurrent program
=====================================================================================

select * from fnd_responsibility_vl
 where responsibility_id in (select distinct responsibility_id
                             from fnd_concurrent_requests where request_id in (655956,655998,656109))


-- Query to get responsibility names for which perticular concurrent program is attached

SELECT responsibility_name
  FROM fnd_responsibility_tl
 WHERE responsibility_id IN (
          SELECT responsibility_id
            FROM fnd_responsibility
           WHERE request_group_id IN (
                    SELECT request_group_id
                      FROM fnd_request_group_units
                     WHERE request_unit_id IN (
                              SELECT concurrent_program_id
                                FROM fnd_concurrent_programs_vl
                               WHERE user_concurrent_program_name LIKE
                                        'Blankets with Metal Locked Beyond 2 Months (RPNA)')))


14). Query to know concurrent program parameter and its value set name
======================================================================

select dfs.descriptive_flexfield_name, dfs.end_user_column_name,fvs.flex_value_set_name
from fnd_flex_value_sets fvs,FND_DESCR_FLEX_COL_USAGE_VL  dfs
where fvs.flex_value_set_id= dfs.flex_value_set_id 
and dfs.end_user_column_name like 'Vendor%'
and dfs.descriptive_flexfield_name like '%XXARS_00065%' --provide program short name here

 
15). Query to delete concurrent program and executable
======================================================

begin
fnd_program.delete_program('XXARS_00125_XML','XXARS');
fnd_program.delete_executable('XXARS_00125_XML','XXARS');
commit;
End;

16). How to Ensure Workflow File Customizations/Changes Are Not OverWritten By Upgrades/Patches [ID 1343956.1]
==============================================================================================================

Applies to:
Oracle Purchasing - Version: 12.1.3 and later   [Release: 12.1 and later ]
Information in this document applies to any platform.
Goal

How can one ensure Oracle Workflow Files are customized correctly so that the customizations (or changes to Workflow Attributes) are not overwritten when patching or upgrading?
Solution
Here are key suggestions so that customizations are not overwritten by patching or upgrades that provide new workflow file versions. 

When creating or modifying a new process, new function, new notification, etc (this includes changing the default value of an Workflow attribute), please proceed as follows:

1) Open the Workflow Builder client software and go to Help > About Oracle Workflow Builder

2) Set the Access Level = 100 and check "Allows modifications of customized objects"

3) If you are making changes to workflow attribute values, please proceed as follows (an example could be modifying the Requisition Approval attribute called "Send PO Autocreation to Background" from the seeded value Y to the desired value N) :

a, Find the workflow attribute that needs to be changed
b. Right click and then choose Properties and modify the value as desired. 
c. Then click on the Access tab and you should see that

Customization = 0
Access = 100
Protection = 1000

d. Check the "Preserve Customizations" check box and the Customization level will change to 100. 
e. Click Apply and save to the database

4) If creating any new objects, ensure they have the following settings (they should default this way, but check to be sure)

a. Right click on the customized process/function and choose Properties
b. Click Access
c. The fields below should be confirmed
Customization = 100
Access = 100
Protection = 100 
Options: Preserve Customizations is checked
NOTE:  You can uncheck Lock at This Access Level if desired, which will set the Protection = 1000

d. Click Apply and Save to the database

Now, when patches or upgrades are done, if a new version of the modified workflow needs to be applied, the patch will use WFLOAD in UPGRADE mode and the workflow objects modified as above will be preserved.


17. Update Mailer Address:
==========================

select fscpv.parameter_value
from fnd_svc_comp_params_tl fscpt
,fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id;

update
fnd_svc_comp_param_vals fscpv
set
fscpv.PARAMETER_VALUE = ''
where
fscpv.parameter_id in (
select
fscpt.parameter_id
from
fnd_svc_comp_params_tl fscpt
where
fscpt.display_name = 'Test Address');


18. To Get the Employee/Supervisor Hierarchy
=================================================

SELECT  paf.supervisor_id
                   , paf.person_id
                   , ( SELECT attribute4
                           FROM per_all_people_f per
                           WHERE paf.supervisor_id = per.person_id
                             AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date
                     ) grade
                     ,  ( SELECT full_name
                           FROM per_all_people_f per
                           WHERE paf.supervisor_id = per.person_id
                             AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date
                     ) full_name
               FROM per_all_assignments_f paf
              START WITH paf.person_id = :p_person_id
                AND paf.primary_flag = 'Y'
                AND paf.assignment_type = 'E'
                AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
                AND assignment_status_type_id = 1
                CONNECT BY PRIOR paf.supervisor_id = paf.person_id
                AND paf.primary_flag = 'Y'
                AND paf.assignment_type = 'E'
                AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
                AND   paf.supervisor_id IS NOT NULL
                AND assignment_status_type_id = 1;