Property To Make DB JNDI Dynamic

Suppose you created a project by using the DB adapter.And you are migrating the project from DEV to TEST.In the TEST environment the JNDI name may be different.So in these situations we have to make the JNDI name dynamic.


Steps to change DB JNDI dynamically.
1.Create a preference in the composite.xml
<property name="bpel.preference.dyn_jndi">eis/jndi/mydbjndi"</property>


2.Then drag and drop the DB adapter and configure it.


3.Then open .bpel file , please the property
 <bpelx:inputproperty name="jca.jndi" variable="dyn_jndi"/>


4.Deploy the project , then we can change the JNDI in the runtime,         By setting the preference    value

SOA – Service Oriented Architecture Overview

To start things on SOA, First we need to understand the basic concepts like web service. Because SOA is mainly related with the web services and its orchestration.

Web services describes a standardized way of integrating Web-based applications using the XML, SOAP, WSDL and UDDI open standards over an Internet protocol.

 XML (Extensible Markup Language) is used to tag the data

SOAP (Simple Object Access Protocol) is used to transfer the data

WSDL (Web Service Description Language) is used for describing the services available and

 UDDI (Universal Description, Discovery and Integration) is used for listing what services are available.

In the developers point of view BPEL (Business Process Execution Language) is used for the development and integration of web services.

BPEL is nothing but a XML based language used to describe a web service.XML tags used in the BPEL was standardized by the OASIS (Organization for the Advancement of Structured Information Standards).

Oracle SOA-BPEL Project development Overview -

 Normally we used to create the business logic (Language specific packages like PL/SQL package or Java Package) and the workflow by using the Oracle workflow. BPEL combines both the versions so that we can develop the workflow activities and some extent of the language code, which describes the business logic.

Steps:

a. Create the business process web service by using the IDE like Jdeveloper or Eclipse. Create a project,   then for the business logic in the project IDE automatically creates WSDL, XSD and XML files. All the project files are bundled into .Jar file for the deployment into the server.

b. Deploy BPEL project (.Jar) file on BPEL Process Manager by using Ant Utility or by using the IDE (Jdeveloper) itself.

c. To test the BPEL process, login into weblogic server em console , where we can test our deployed web service.

Query to find the Primary Customer Information based on the org_id

    SELECT *
    FROM   hz_parties hp,
                   hz_cust_accounts hca,
                   hz_party_sites hps1,
                   hz_locations hl1,
                   hz_cust_acct_sites_all hcas1,
                   hz_cust_site_uses_all hcsu1
    WHERE   hp.party_id                = hca.party_id
    AND hp.party_id                      = hps1.party_id
    AND hp.party_id                      = hps1.party_id
    AND hl1.location_id                  = hps1.location_id
    AND hps1.party_site_id          = hcas1.party_site_id
    AND hcas1.cust_account_id    = hca.cust_account_id
    AND hcas1.cust_acct_site_id  = hcsu1.cust_acct_site_id
    AND hcsu1.site_use_code        IN ('BILL_TO','SHIP_TO')
    AND hcsu1.primary_flag         = 'Y'
    AND hcsu1.status                      = 'A'
    AND hca.cust_account_id        = <sold_to_org_id>;

Script To Download and Upload LDT Files For Various Objects

Concurrent Program
*************************************************
FNDLOAD apps/ohs_xnappi 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct Concurrent_Program_Short_Name.ldt PROGRAM  CONCURRENT_PROGRAM_NAME='Concurrent_Program_Short_Name' APPLICATION_SHORT_NAME='ZZZ'
FNDLOAD apps/ohs_xnappi 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct Concurrent_Program_Short_Name.ldt

Value Set
*************************************************
FNDLOAD apps/ohs_xnappi 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct Value_Set_Name.ldt VALUE_SET FLEX_VALUE_SET_NAME='Value_Set_Name'
FNDLOAD apps/ohs_dnappi 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct Value_Set_Name.ldt

Request Set
*************************************************
FNDLOAD apps/ohs_xnappi 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct Request_Set_Name.ldt REQ_SET_LINKS APPLICATION_SHORT_NAME=ZZZ REQUEST_SET_NAME=Request_Set_Name
FNDLOAD apps/ohs_dnappi 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct Request_Set_Name.ldt

Responsiblity
*************************************************
FNDLOAD apps/simple4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct Responsibility_Name.ldt FND_RESPONSIBILITY RESP_KEY="Responsibility_Name"
FNDLOAD apps/simple4u 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct Responsibility_Name.ldt

Lookups
*************************************************
FNDLOAD apps/options4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct Lookup_Name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='AU' LOOKUP_TYPE="Lookup_Name"
FNDLOAD apps/options4u 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct Lookup_Name.ldt

Flex Fields
*************************************************
FNDLOAD apps/ohs_xnappi 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct FlexField_Name_Ldt_File.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="ZZZ" DESCRIPTIVE_FLEXFIELD_NAME="FlexField_Name" P_CONTEXT_CODE="Global Data Elements"
FNDLOAD apps/ohs_xnappi 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct FlexField_Name_Ldt_File.ldt

Form
*************************************************
FNDLOAD apps/ohs_xnappi 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct Form_Name.ldt FORM FORM_APP_SHORT_NAME='ZZZ' FORM_NAME=Form_Name
FNDLOAD apps/ohs_xnappi 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct Form_Name.ldt FORM FORM_APP_SHORT_NAME='ZZZ' FORM_NAME=Form_Name UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Functions
*************************************************
FNDLOAD apps/simple4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct Function_Name_Ldt_File.ldt FUNCTION FUNCTION_NAME=Function_Name
FNDLOAD apps/simple4u 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct Function_Name_Ldt_File.ldt FUNCTION FUNCTION_NAME=Function_Name UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Menus
*************************************************
FNDLOAD apps/simple4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct Menu_Name_Ldt_File.ldt MENU MENU_NAME=Menu_Name
FNDLOAD apps/simple4u 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct Menu_Name_Ldt_File.ldt MENU MENU_NAME=Menu_Name UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Profile Options
*************************************************
FNDLOAD apps/ohs_xnappi 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct Profile_Ldt_Name.ldt PROFILE PROFILE_NAME="profile_option_short_name" APPLICATION_SHORT_NAME="FND"
FNDLOAD apps/ohs_xnappi 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct Profile_Ldt_Name.ldt
-- to download a single profile option value set at some site/user/resp level
FNDLOAD apps/ohs_xnappi O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct Profile_Ldt_Name.ldt PROFILE PROFILE_NAME="Profile_Name" APPLICATION_SHORT_NAME="FND" LEV='RESPONSIBILITY' LEV_NAME='Responsibility_Name'
FNDLOAD apps/ohs_xnappi 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct Profile_Ldt_Name.ldt

Query To Fetch Concurrent Program Names Based On The ValueSet

SELECT   b.user_concurrent_program_name
  FROM    fnd_descr_flex_col_usage_vl a,
         fnd_concurrent_programs_vl b
 WHERE  a.flex_value_set_id = ( SELECT    flex_value_set_id
    FROM    fnd_flex_value_sets
 WHERE    flex_value_set_name = '&Value_Set_Name')
   AND     a.descriptive_flexfield_name = '$SRS$.' || b.concurrent_program_name

Analyzing The Queries Through Concurrent Program Request ID (Performance Testing)

Query : 1
SELECT  user_concurrent_program_name,
c.request_id,
s.sid,
p.spid,
s.process,
s.osuser,
s.username,
s.program,
s.status,
logon_time,
last_call_et
FROM v$session s, 
v$process p, 
apps.fnd_concurrent_requests c,
apps.fnd_concurrent_programs_tl ct 
WHERE oracle_process_id=p.spid 
AND s.paddr=p.addr 
AND ct.concurrent_program_id=c.concurrent_program_id
AND request_id='&Request_Id';

Query : 2

SELECT    sql_text,
          lockwait
FROM      v$session a, v$sql b
WHERE    a.sql_address = b.address
AND         a.sql_hash_value = b.hash_value
AND         a.sid = '&SID';

Queries To Find & Release Locks At Object Level

Search Locks For the Object

Step :1) SELECT  session_id 
             FROM     dba_ddl_locks
             WHERE  name = '<object_name_here>';

Look For the SID and SERIAL# Combination For The Corresponding SESSION_ID
Step :2) SELECT  sid,serial# 
             FROM     v$session 
             WHERE  sid = '<session_id>';

Query To Kill DB Session
Step :3) alter system kill session '<sid>,<serial#>';

Query To Find Concurrent Program Attached To a Responsibility

SELECT frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM fnd_Responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
  AND     frg.request_group_id = fr.request_group_id
  AND     frgu.request_group_id = frg.request_group_id
  AND     fcpt.concurrent_program_id = frgu.request_unit_id
  AND     fcpt.user_concurrent_program_name = '&conc_program_name'
ORDER BY 1,2,3,4

Deleting Concurrent Program In Oracle Apps

DECLARE

   v_program_short_name    VARCHAR2 (200);

   v_application                   VARCHAR2 (200);

BEGIN

   v_program_short_name := 'My_Concurrent_Program';

   v_application                := 'My_Application';

   apps.fnd_program.delete_program

               (program_short_name      => v_program_short_name,

                application                     => v_application

               );

   COMMIT;

END;

Second Max Salary Query

SELECT DISTINCT (A.SAL) 

FROM SCOTT.EMP A 

WHERE &N = (SELECT COUNT (DISTINCT (B.SAL)) FROM SCOTT.EMP B WHERE A.SAL<=B.SAL);