Friday, March 27, 2015

jazn-data.xml for webcenter

The system-jazn-data.xml is an XML file which is configured by the user to use as an ID store and/or policy store. The file is located in $DOMAIN_HOME/config/fmwconfig.
Sample system-jazn-data.xml file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<jazn-data>
  <jazn-realm>
    <realm>
      <name>jazn.com</name>
        <users>
          <user>
            <name>weblogic</name>
            <guid>23AAB190021911DDBF86C74F01C202FB</guid>
            <credentials>PN0Qr+/dpDRV+jSWP378EdjxWDS0PuAs=</credentials>
          </user>
          </users>
      </realm>
    </jazn-realm>
    <policy-store>
      <applications>
        <application>
          <name>reports</name>
            <app-roles>
              <app-role>
                <name>rw_administrator</name>
                <display-name>Reports Administrator</display-name>
                <class>oracle.security.jps.service.policystore.ApplicationRole</class>
                <members>
                  <member>
                    <class>oracle.security.jps.internal.core.principals.JpsXmlUserImpl</class>
                    <name>weblogic</name>
                  </member>
                  <member>
                    <class> weblogic.security.principal.WLSUserImpl </class>
                    <name>weblogic</name>
                  </member>
                </members>
              </app-role>
              <app-role>
                <name>rw_operator</name>
                <display-name>Reports Operator</display-name>
                <class>oracle.security.jps.service.policystore.ApplicationRole</class>
              </app-role>
              <app-role>
                <name>rw_monitor</name>
                <display-name>Reports Monitor</display-name>
                <class>oracle.security.jps.service.policystore.ApplicationRole</class>
              </app-role>
            </app-roles>
            <jazn-policy>
              <grant>
              <grantee>
                <principals>
                  <principal>
                    <class>oracle.security.jps.service.policystore.ApplicationRole</class>
                    <name>rw_administrator</name>
                  </principal>
                </principals>
              </grantee>
              <permissions>
                <permission>
                  <class>oracle.reports.server.ReportsPermission</class>
                  <name>report=* server=* destype=* desformat=* allowcustomargs=true</name>
                  <actions>*</actions>
                </permission>
                <permission>
                  <class>oracle.reports.server.WebCommandPermission</class>
                  <name>webcommands=* server=*</name>
                  <actions>execute</actions>
                </permission>
              </permissions>
            </grant>
            <grant>
              <grantee>
                <principals>
                  <principal>
                    <class>oracle.security.jps.service.policystore.ApplicationRole</class>
                    <name>RW_BASIC_USER</name>
                  </principal>
                </principals>
              </grantee>
              <permissions>
                <permission>
                  <class>oracle.reports.server.ReportsPermission</class>
                  <name>report=test.rdf server=* destype=* desformat=* allowcustomargs=true</name>
                  <actions>*</actions>
                </permission>
                <permission>
                  <class>oracle.reports.server.WebCommandPermission</class>
                  <name>webcommands=showmyjobs,getjobid,showjobid server=*</name>
                  <actions>execute</actions>
                </permission>
              </permissions>
            </grant>
          </jazn-policy>
        </application>
</applications>
</policy-store>

Thursday, March 26, 2015

Workflow related tables and basic on how fix HR related workflow issue

WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modelled workflow process, which can be included as an activity in other processes to represent a sub process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.

The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.

hr_api_transactions is a place to hold the HR workflows in process

To remove a workflow do the following
execute hr_transaction_api.rollback_transaction( p_transaction_id => 3350154, p_validate => false);
commit;
execute wf_engine.abortprocess  (itemtype => 'HRSSA',  itemkey => '235642');
commit;
execute wf_purge.items (itemtype => 'HRSSA',itemkey => '235642' );
commit;

Tuesday, March 24, 2015

Find true field start/end date from date tracked tables like per_people_f, per_assignments_f



There's time you want to know what are the start date/end date an employee stay within a job, sometimes it is not an easy task because an employee may have multiple assignments records because of org change, supervisor change or so, even though all assignments records have same job. Here is a way to find out what is the true job (or other fields) start/end date which is using analytic function. 



Here is the example of data in Assginments table with a group id. The group id is a date, it is calculated using This analytic function: 

--+++++++++++++++++++++++++++++++++++++++++++++
effective_end_date-sum(effective_end_date-effective_start_date+1) over(partition by person_id, job_id order by effective_start_date) +1 group_id
--+++++++++++++++++++++++++++++++++++++++++++++


id        job_id    date_start    date_end      group_id
814    12219    8/22/2005    3/31/2006    8/22/2005
814    12579    7/1/2007    9/30/2007    7/1/2007
814    12579    10/1/2007    10/21/2007    7/1/2007
814    12579    10/22/2007    3/31/2008    7/1/2007
814    12579    4/1/2008    1/7/2010    7/1/2007
814    12579    1/8/2010    2/21/2010    7/1/2007
814    12579    2/22/2010    5/31/2010    7/1/2007
814    12579    6/1/2010    6/23/2010    7/1/2007
814    12579    6/24/2010    3/31/2012    7/1/2007
814    12579    4/1/2012    5/6/2012    7/1/2007
814    12579    5/7/2012    10/1/2013    7/1/2007
814    12579    5/5/2014    12/31/4712    2/1/2008
814    12622    4/1/2006    3/31/2007    4/1/2006
814    12622    4/1/2007    4/5/2007    4/1/2006
814    12623    4/6/2007    6/30/2007    4/6/2007
814    12623    2/18/2014    5/4/2014    11/24/2013


Please pay attention to that the same job ids 12579 is in different groups, one group is 7/1/2007 and another is 2/1/2008. The group id does not mean a real date, it is just the result of a calculation and is used to differenciate job range. 

As soon as you generate the list, you can now using group by to generate the range of each job group: 

814    12219    8/22/2005    3/31/2006
814    12622    4/1/2006    4/5/2007

814    12623    4/6/2007    6/30/2007

814    12579    7/1/2007    10/1/2013

814    12623    2/18/2014    5/4/2014
814    12579    5/5/2014    12/31/4712


Here is the query: 

select person_id,job_id,min(effective_start_date) job_start_date, max(effective_end_date) job_end_date
from    (select     person_id, job_id, effective_start_date, effective_end_date
                       , effective_end_date-sum(effective_end_date-effective_start_date+1) over (partition by person_id, job_id order by effective_start_date) +1 group_id
            from     per_assignments_f
            where   assignment_status_type_id in
                                   --only pick up those assignments active assignments
                                                 (1 --Active Assignment
                                                 ,2 --Active Contingent Assignment
                                                 ,3 --Leave with Pay Assignment
                                                 ,4 --Leave without Pay Assignment
                                                 ,5)
             and person_id=814
             )TMP
group by person_id,job_id,group_id



Using SQL Minus to compare the difference of two tables

THE SQL MINUS OPERATOR IS USED TO RETURN ALL ROWS IN THE FIRST SELECT STATEMENT THAT ARE NOT RETURNED IN THE SECOND SELECT STATEMENT.

Each SELECT statement within the MINUS query must have the same number of fields in the result sets with similar data types.

SYNTAX

The syntax for the SQL MINUS operator is:
SELECT expression1, expression2, ... expression_n
FROM tables
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables;

EXAMPLE - WITH SINGLE EXPRESSION

The following is a SQL MINUS operator example that has one field with the same data type:
SELECT supplier_id
FROM suppliers
MINUS
SELECT supplier_id
FROM orders;
This SQL MINUS example returns all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.

EXAMPLE - USING ORDER BY CLAUSE

The following is a MINUS operator example that uses the ORDER BY clause:
SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
MINUS
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;
In this SQL MINUS operator example, since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
The supplier_name / company_name fields are in position #2 in the result set.

Monday, March 23, 2015

Remove special characters from string using regexp functions

There is a need to remove special characters and non-printable characters that users have accidentally entered into CSV files. For every varchar2 field, I'd like to keep characters inside the range from chr(32) to chr(126), and remove everything else. 

This is the query to solve the problem, it use regexp functions

select regexp_replace('This is a test $%&^*&*'||chr(10)||'*"รบ%bsfd.', '( *[[:punct:]])', '') from dual

I really like how regexp functions can do, for more information, check more from here:  http://psoug.org/reference/regexp.html

Wednesday, March 18, 2015

Submit a Concurrent Request from PL/SQL script

We can submit a concurrent request from backend using fnd_request.submit_request API. Before submitting the API we need to set the environment context using fnd_global.apps_initialize
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/****************************************************************
*PURPOSE: To Submit a Concurrent Request using PL/SQL script    *
*Using fnd_request.submit_request                                                   *
****************************************************************/

DECLARE
l_responsibility_id  NUMBER;
l_application_id     NUMBER;
l_user_id            NUMBER;
l_request_id            NUMBER;
BEGIN
  --Find correct responsibility_id and application_id
  SELECT DISTINCT fr.responsibility_id,
    frx.application_id
     INTO l_responsibility_id,
    l_application_id
     FROM apps.fnd_responsibility frx,
    apps.fnd_responsibility_tl fr
    WHERE fr.responsibility_id = frx.responsibility_id
  AND LOWER (fr.responsibility_name) LIKE LOWER('XXTest Resp');
  --
   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'SYSADMIN';
  --
  --To set environment context.
  --
  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);
  --
  --Submitting Concurrent Request
  --
  l_request_id := fnd_request.submit_request ( 
                            application   => 'XXCUST', 
                            program       => 'XXCONCURRENT', 
                            description   => 'XXTest Conccurent Request', 
                            start_time    => sysdate, 
                            sub_request   => FALSE,
       argument1     => '12/31/2012 00:00:00'
  );
  --
  COMMIT;
  --
  IF l_request_id = 0
  THEN
     dbms_output.put_line ('Concurrent request failed to submit');
    --using fnd log if the pl/sql script is run within concurrent manager
    fnd_file.put_line (fnd_file.log, 'Concurrent request failed to submit');
  ELSE
     dbms_output.put_line('Successfully Submitted the Concurrent Request');
  END IF;
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;
/

Sample scripts for FNDLOAD

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
<apps/pwd>: The APPS schema and password in the form username/password[@connect_string]. If connect_string is omitted, it is taken in a platform-specific manner from the environment
using the name TWO_TASK.
< 0 Y >: Concurrent program flags.<mode>: UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.<configfile>: The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader).<datafile>: The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.<entity>: The entity(ies) to upload or download. When uploading, always upload all entities, so specify a "-" to upload all entities.< [param] >: Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.

Note: Objects enclosed in [ ] are optional and may be used for more precise download or upload.

Some Sample Examples:  
FNDLOAD Concurrent Program
Download:
FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct your_output_dir/your_name.ldt PROGRAM [APPLICATION_SHORT_NAME=$app] CONCURRENT_PROGRAM_NAME="SHORT_PROGRAM_NAME"
Upload:
FNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct
your_output_dir/your_name.ldt

FNDLOAD Profile Options
Download
FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct your_output_dir/your_name.ldt PROFILE PROFILE_NAME="profile_option_short_name"[APPLICATION_SHORT_NAME=app]
Upload
FNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct your_output_dir/your_name.ldt

FNDLOAD Value Set
Download
FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct your_output_dir/your_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value_set_name"
Upload
FNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct your_output_dir/your_name.ldt

FNDLOAD Request Group 
Download
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct your_output_dir/your_name.ldt REQUEST_GROUP REQUEST_GROUP_CODE="REQUEST_GROUP_CODE" 
Upload
FNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct your_output_dir/your_name.ldt

FNDLOAD Request Set
Download
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct your_output_dir/your_name.ldt REQ_SET REQUEST_SET_NAME="REQUEST_SET_NAME"
Upload
FNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct your_output_dir/your_name.ldt

FNDLOAD Responsibility 
Download

  

FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct your_output_dir/your_name.ldt FND_RESPONSIBILITY RESP_KEY="RESPONSIBILITY_KEY_VALUE"
Upload
FNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct your_output_dir/your_name.ldt

FNDLOAD User Record
Download
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct your_output_dir/your_name.ldt FND_USER USER_NAME="USERNAME_TO_PROCESS"
Upload
FNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct your_output_dir/your_name.ldt 

FNDLOAD Message
Download
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct your_output_dir/your_name.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='app' [MESSAGE_NAME="MESSAGE_NAME"] 
Upload
FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct your_output_dir/your_name.ldt
 
FNDLOAD Menu
Download  
FNDLOAD apps/appspwd  0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="MENU_NAME"
Upload
FNDLOAD apps/appspwd  0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt

FNDLOAD Printer Style 
Download:
FNDLOAD apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct your_output_dir/your_name.ldt STYLE PRINTER_STYLE_NAME="PRINTER_STYLE_NAME" 
Upload:
FNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct