How to delete records from wf_notifications table

DECLARE

CURSOR csr_transaction_id IS
SELECT   hat.transaction_id,
         hat.item_type,
         hat.item_key,
         ppx.employee_number,
         hat.section_display_name
  FROM   hr_api_transactions hat, per_people_x ppx
 WHERE   hat.process_name = 'HR_PERSONAL_INFO_JSP_PRC'
         AND hat.selected_person_id = ppx.person_id
         AND ppx.employee_number IN
                  ('100024773',
                   '100024820',
                   '100024859',
                   '100024879',
                   '100024902',
                   '100024937',
                   '100025137',
                   '100026470',
                   '610014755',
                   '610017039')
 order by  ppx.employee_number;
BEGIN
  dbms_output.put_line('***Deleted all Transactions  and Notifications of below Employee Personals Tranactions ***'); 
   FOR my_cur_v IN csr_transaction_id
   LOOP
   /*Delete all Transaction_id's in hr_api_transactions,hr_api_transaction_steps,hr_api_transaction_values and hr_api_transaction_steps_bk tables */
    hr_transaction_swi.delete_transaction
                          (
                          p_transaction_id =>my_cur_v.transaction_id,
                          p_validate => hr_api.g_false_num
                          );
                                   

    wf_engine.abortprocess  (
                              itemtype => my_cur_v.item_type,
                             itemkey => my_cur_v.item_key
                             );
     /* Deleted all Notification_id's and item_key's in wf_item_activity_statuses,wf_items,wf_item_attribute_values,wf_notifications     Table */                      
     wf_purge.items (
                      itemtype => my_cur_v.item_type,
                     itemkey => my_cur_v.item_key
                          );                                    
    dbms_output.put_line('Emp No --'||my_cur_v.employee_number||'Transaction_id :'||my_cur_v.transaction_id||'Emp Personal Info :'||my_cur_v.section_display_name||
                          'Item Type :'||my_cur_v.item_type|| 'Item Key :'||my_cur_v.item_key);  
                               
   END LOOP;
   commit;
EXCEPTION 
WHEN OTHERS THEN
  dbms_output.put_line('hr_transaction_swi.delete_transaction api goest to exception block'    ||sqlcode|| '  '||sqlerrm);
END;   
/

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

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
DownloadFNDLOAD 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]
UploadFNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct your_output_dir/your_name.ldt

FNDLOAD Value Set
DownloadFNDLOAD 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"
UploadFNDLOAD apps/password O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct your_output_dir/your_name.ldt

FNDLOAD Request Group 
DownloadFNDLOAD 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" 
UploadFNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct your_output_dir/your_name.ldt

FNDLOAD Request Set
DownloadFNDLOAD 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"
UploadFNDLOAD 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"
UploadFNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct your_output_dir/your_name.ldt

FNDLOAD User Record
DownloadFNDLOAD 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"
UploadFNDLOAD 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

Connect Oracle using ODBC 32 bit in 64 bit Windows 7

For some reason I need to build an ODBC connection to Oracle database, the Oracle database version is 11.2.0.2. This turned out to be quite a big challenge. There are several issues during the installation, as usual, Oracle never make things easy. Anyway, here is how:

1. Download Instant Client basic 32 bit and ODBC 32 bit for 11.2.0.2 from here.
2. Extract both zip files you downloaded, put the files in instantclient_11_2 folder into C:\instantclient_11_2\
3. Add C:\instantclient_11_2 into Windows Path
4. Run C:\instantclient_11_2\odbc_install.exe
    You may see a warning from Program Compatibility Assistant says: This program might not be installed correctly. Just ignore it.
5. Goto ODBC 32bit console, tt is not easy to find Windows ODBC 32 bit console, the fastest way is to run C:\Windows\SysWOW64\odbcad32.exe from Start-&gt;search
6. If you have ODBC 32bit console open, create a new ODBC connection using Oracle in instantclient_11_2. You will fill the TNS Service Name like this: //host_ip_or_url:port/instance_name.
6. At this point you can test the new ODBC connection, but mostly you will see this error:
    ORA-12705: Cannot access NLS data files or invalid environment specified
The solution was to find the registry key named NLS_LANG under:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE
and rename it to anything else. I put an "x" at the end. This key is left over from a full install of Oracle. The instant client ODBC works properly now.

Oracle Workflow tables

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.

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.

220: Unable to set NLS_LANGUAGE Error When Connecting to the Database From Oracle Workflow Builder

Set NLS_LANG as an operating system environment variable:

1. Select Start - Control Panel - System

2. Select Environment 

3. Set NLS_LANG parameter to AMERICAN_AMERICA.UTF8

Understand Oracle EBS Multi Org Structure and difference between 11i and R12

MULTI ORG STRUCTURE IN 11i:
--------------------------------------
Multi Organization is a feature of oracle applications which Provides Implementation,
Maintenance and operations of Multiple Business Units in an Enterprise within a single
installation of Oracle Applications, taking Appropriate care of Data security and data separation.
Below mentioned are certain salient features of Multi Organization functionality.

                                                               Enterprise
                                                                 |
Businessgroup                                   India                         Usa
                                                            |                                |
setofbooks                         company1       company2    company3
                                                 |                   |                |
legalentitys                              LE1             LE2          LE3
                                                  |                  |                 |
Operating unit                          OU1           OU2         OU3
org_id                                         |                    |               |
invorganization                    warehouse1    warehouse2   warehouse3
organization_id

FEATURES OF MULTI ORG:

->Allows multiple sets of books and multiple legal entities to be configured and to operate in the same instance.

->Provides support for data security between business units within a single applications installation.

->Permits users to sell and ship products from different legal entities (in different sets of books)
with automatic intercompany accounting

->Supports internal requisitions and purchasing/receiving products from different inventory organizations
(within the same set of books)

->Enables an enterprise to be housed in one database instance of Oracle, spanning multiple countries,
currencies, and legal entities without a reduction in response times (architecture-related)

->Multiple Organizations Reporting enhances the reporting capabilities of Oracle Applications products
by allowing you to report at the: Set of Books level, Legal entity level or Operating unit level

MULTI ORG STRUCTURE IN R12:

MOAC feature ensures a role based access to the organizations defined in the Multi-Org structure.
This essentially means that individuals can access/view/edit forms across organizations without switching responsibility. (The level of access and flexibility in terms of actions on the forms will depend on the setups, which will be discussed going forward.)

The steps involved in created a MOAC are:
(check this vedio, it has some good information) http://youtu.be/wAU-dowvf00
--------------------------------------------
a. Create Primary ledger's
b. Create Legal Entities and associate these with Primary Ledgers
c. Create Operating Units and associate these with Primary Ledgers
d. Create responsibilities
e.  Attach these responsibilities to users
f.  Create a Security profile
g. Attach  the security profile to the responsibilities
h. Setup OU preference

Features:
---------
->MOAC (Multi Org Access Control)
->The Set of Books evolved into Ledgers and Ledger Sets
->Any number of Business Units in an Enterprise can be supported within a single installation of Oracle Applications even if they have different Ledger, Legal Entity, HR data
->User can access the data corresponding to and limited to the operating unit of the user.
->Transactions like Procurement, Receiving, Selling, Shipping Etc. with the same Party Can be Performed through different Organization and can be managed internally through intercompany postings.
->Reporting can be managed at different organization levels like, Business Group, Ledger, Operating unit etc.

Difference between 11i and r12: 
                 11i                                             R12
------                                        ----------
->set of books                                            ledgers
->3C's calender,chartofaccounts                 4C's calender,chartofaccounts and currency
and currency                                               and conversions(accounting method)
->Global accounting engine                         Sub ledger accounting method (SLAM)
->global intercompany system(GIS)             Advanced globalintercompany system(AGIS)
->Primary set of books                                 Primary Ledger
->reporting set of books                               reporting currency
->MRC set of books                                      Secondary ledger

what are the major difference between oracle 11i and R12 ?

Ans :

Ø      11i only forms basis application but R12 is now forms and HTML pages.

Ø      11i is particularly in responsibility and operating unit basis but R12 is multi operating unit basis.

Ø      11i is particularly in set of books using and R12 using in ledgers.

Ø      11i in MRC Reporting level set of books called reporting set of books but in R12 reporting ledgers called as reporting currency. Banks are using at single operating unit level in 11i and ledgers level using in R12.

Differences between R12 & 11i.5.10 New R12 Upgrade to R12 – Pros and Cons Pros:

Ø      Sub-ledger Accounting – The new Oracle Sub-ledger Accounting (SLA) architecture allows users to customize the standard Oracle accounting entries. As a result of Cost Management's uptake of this new architecture, users can customize their accounting for Receiving, Inventory and Manufacturing transactions.

Ø      Enhanced Reporting Currency (MRC) Functionality – Multiple Reporting Currencies functionality is enhanced to support all journal sources. Reporting sets of books in R12 are now simply reporting currencies. Every journal that is posted in the primary currency of a ledger can be automatically converted into one or more reporting currencies.

Ø      Deferred COGS and Revenue Matching – R12 provides the ability to automatically maintain the same recognition rules for COGS and revenue for each sales order line for each period. Deferred COGS and Deferred Revenue are thus kept in synch.

References: 

For Multi org Setups:
http://alloracleapps.com/oracle_apps/oracleappsappsfunctionalfunctionalmoduleshrandfi/


Upgrade Oracle application  from 11i to R12:
http://www.reachdba.com/showthread.php?741-Upgrade-Oracle-Applications-from-11i-to-R12


How to find whether MULTI_ORG is enabled for the application or not?

SQL> Select * FROM FND_PRODUCT_GROUPS

Columns in the table FND_PRODUCT_GROUPS:

RELEASE_NAME

PRODUCT_GROUP_TYPE

MULTI_ORG_FLAG

MULTI_LINGUAL_FLAG

MULTI_CURRENCY_FLAG

From the backend, How to set Client Info in Backend?

Ø  DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('org_id');

                   Ex:     DBMS_APPLICATION_INFO.SET_CLIENT_INFO (103);

Ø  mo_global.set_policy_context('S',81);

Ø  FND_CLIENT_INFO.SETUP_CLIENT_INFO (application_id => <application_id>

                                       ,responsibility_id =><Resp Id>

                                       ,user_id =><User id>

                                       ,security_group_id =><Security Group Id>);

Ex:

FND_CLIENT_INFO.SETUP_CLIENT_INFO(application_id => 660,

                                                          responsibility_id =>21623,

                                                          user_id =>0,

                                                          security_group_id =>0);

How to set Client Info in Reports?

Before Report: DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('org_id');

More about profile option setting check here: http://oracleappsdbahelp.blogspot.in/2012/08/profile-options-in-oracle-apps-r12.html