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

Note for building OBIEE sample APP Virtual Machine

1. Following SampleAppQuickDeploymentGuide-309-R2.pdf to build the Virtual Box

2.1 For OBIEE Admin Client tool. Log into this machine's web page (port 9704/analytics). Download the tool from there. Doing this will make sure the client tool version is same as server version. 

2.2 Make sure the computer that Client tool installed, the computer should have host file include: 

    192.168.0.105(OBIEE server ip) obieesample.us.oracle.com

2.3 create tnsnames.ora and sqlnet.ora by copying those from OBIEE server's $ORACLE_HOME/network/admin to \oraclebi\orahome\network\admin, you will need to create this folder. Make sure tnsnames.ora host=obieesample.us.oracle.com, not ip address or others. 

2.4 copy the missing dll files to \oraclebi\orahome\bifoundation\server\bin from dll folder

3.  Study 

    start from here: http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/tutorials/obiee11g-453435.html

    and pick this as the first tutorial:"Creating a Repository Using the Oracle BI 11g Administration Tool"


You can find SampleApp-QuickDeploymentGuide-309-R2.pdf 
and SampleAppv309-R2-UserGuide.pdf from Oracle web site

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

How to find Components version in R12

Apache Version

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v

Go to reports path Then type: -

string -a APXAPRVL.rdf|grep Header

Perl Version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built

Java Version

sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version

cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Plsql Version

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=

echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

How to find Apps Version
select release_name from apps.fnd_product_groups;

Web Server/Apache or Application Server in Apps 11i/R12
Log in as Application user, set environment variable and run below query$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
  
Forms & Report version in R12/12i
Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release

 Oracle Jinitiator in 11i/R12/12i
 Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )

Oracle Java Plug-in in 11i/R12/12i
A. Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE.

File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check

adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Workflow Version with Apps
select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
  
Identity Management component Version/Release Number

Oracle Single Sign On
select version from orasso.wwc_version$;

Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)

To find software/binary version
$ORACLE_HOME/bin/oidldapd -version

To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \ -s base “objectclass=*” orcldirectoryversion 

select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;

 Application Server
Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0 

For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database

select * from ias_versions;
or
select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;

AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version 

Oracle Portal
select version from portal.wwc_version$; 

Database Component 

To find database version
select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail

Unix Operating System

Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release