Wednesday, March 12, 2014

API to add the responsibility to the user



bEGIN
fnd_user_pkg.addresp(username => 'VINOTH'
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 1000);
COMMIT;
END;

Friday, February 28, 2014

OM to AR and SC to AR Linking queries

OM to AR Table linkage
==================
SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    RCTLA.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR (OOHA.ORDER_NUMBER)
AND    order_number =  :p_order_number;

SC to AR
=======
SELECT * FROM APPS.RA_INTERFACE_LINES_ALL WHERE BATCH_SOURCE_NAME = 'OKS_CONTRACTS'
AND INTERFACE_LINE_ATTRIBUTE1 = :p_contract_number;

SC Billing details
==============
SELECT hdr.contract_number "Contract"
, hdr.contract_number_modifier "Modifier"
, hdr.id
, TO_CHAR( cont.creation_date, 'DD-MON-YYYY HH24:MI') "Creation Date"
, bill_action
, cont.btn_id "Billing Transaction ID"
, amount
, TO_CHAR( DATE_BILLED_FROM, 'DD-MON-YYYY' ) "Date Billed From"
, TO_CHAR( DATE_BILLED_TO, 'DD-MON-YYYY' ) "Date Billed To"
, obt.trx_date
, obt.trx_amount
, obt.trx_number
, obt.trx_amount
FROM apps.oks_bill_cont_lines cont
, apps.okc_k_lines_b line
, apps.okc_k_headers_b hdr
, apps.oks_bill_transactions obt
, APPS.oks_bill_txn_lines obtl
WHERE hdr.id = line.dnz_chr_id
AND cont.cle_id = line.id
AND HDR.CONTRACT_number =:p_contract_number
AND CONT.BTN_ID = OBT.ID (+)
AND OBTL.BTN_ID (+) = CONT.BTN_ID
ORDER BY CONT.CREATION_DATE;

Wednesday, October 30, 2013

OAF Controller Extension

How to extend a standard controller CO?

I have a standard page in HRMS module and  have a business requirement to have additional check(Validations) to be performed before i click Transfer button. Transfer button Will have a standard controller.

How to find the controller Name?
Click About this page hyperlink displayed at bottom of the page where your controller has to be extended.

Note: FND: Diagnostics (FND_DIAGNOSTICS) profile option to Yes to render the "About this page" link at the bottom of each OA Framework-based page.

1)Unbook the Business components hierarchy
2)It will display all the BC4J components of the OA page.
3)Find the controller at $JAVA_TOP/oracle/apps/per/wpm/objectives/webui
4)Download the controllerCO.class file from "oracle/apps/per/wpm/objectives/webui" to your local system. Your controller will be applicable based on the application top.
5)Decompile the class file to get java file using CAVAJ utility.
6)Open the Original Java file and Start modify the processFormRequest( ) Method. I have highlighted the changes done to achieve the business requirement.

 package xx.oracle.apps.per.wpm.objectives.webui;

import com.sun.java.util.collections.HashMap;
import java.io.Serializable;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.beans.form.OASubmitButtonBean;
import oracle.apps.per.selfservice.common.SSHRParams;
import oracle.jbo.domain.Number;
import oracle.apps.per.wpm.objectives.webui.*;
import oracle.apps.fnd.framework.OAFwkConstants;
import oracle.apps.fnd.framework.server.OADBTransaction; //to establish DB connection
import java.sql.CallableStatement; //to execute a package.procedure
import java.sql.Types;//used for type conversions and VARCHAR2



// Referenced classes of package oracle.apps.per.wpm.objectives.webui:
//            ObjectivesPageCO

public class XXSetObjectivesPageCO extends SetObjectivesPageCO
{

    public static final String RCS_ID = "$Header:" +
" ship $"
;
    public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header:" +
" ship $"
, "oracle.apps.per.wpm.objectives.webui");

    public XXSetObjectivesPageCO()
    {

           //Constructor;
    }


    public void processRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
    {
   
      super.processRequest(oapagecontext, oawebbean);
    //I need standard processRequest method to be executed first.
    }

   
    public void processFormRequest(OAPageContext oapagecontext, OAWebBean oawebbean)
    {
      String p_return_msg  = null;
      if(oapagecontext.getParameter("MgrTransfer") != null) //This is the Transfer Button ID name
       {
        OADBTransaction tx = oapagecontext.getRootApplicationModule().getOADBTransaction();
    
                  String sql="BEGIN " +
                 "XXValidateProc("+
                                   "p_param_1=> :1,"+    
                                   "p_return_msg => :2);"+
                                   "       END;";           
          CallableStatement cStmt = (CallableStatement)tx.createCallableStatement(sql, 1);
          if(cStmt!=null) 
               {
                         try { 
                       
                         cStmt.setString(1,oapagecontext.getDecryptedParameter("
p_param_1"));      
                         cStmt.registerOutParameter(2,Types.VARCHAR);                            
                         cStmt.execute();                                                           
                         p_return_msg = cStmt.getString(2);                                                  
                         cStmt.close();                                                             
                         }
                        catch(Exception e)
                        {
                          throw new OAException("SBM: Error Calling XXValidateProc" + e.toString(),OAException.ERROR);
                        }
                                         
                }
        if(!p_return_msg.equalsIgnoreCase("No Errors")){
                  throw new OAException(p_return_msg,OAException.ERROR);                                           
        }
        else
        {
                         super.processFormRequest(oapagecontext, oawebbean);
        }       
       
     
    }
    else

   { //In case of any exception in the custom procedure, I want the standard controller method's to be called.
     super.processFormRequest(oapagecontext, oawebbean);
   }
     
    }
    }
   
Migration of OA Controller Extension

1)Create a folder under $JAVA_TOP xx/oracle/apps/per/wpm/objectives/webui;
2)Move the  XXSetObjectivesPageCO.java to the above location.
3)Compile the Java using javac XXSetObjectivesPageCO.java
4)Once compilation is done successfully.
5) Goto the page where the Controller is extended. Click Personalize Page Option.

Note: Personalize Self-service Defn (FND_CUSTOM_OA_DEFINTION) profile option should be enabled.


 Go to The regions where controller is got extended. Inherit the Controller location as

xx/oracle/apps/per/wpm/objectives/webui/XXSetObjectivesPageCO

Controller Extension is Done :)

Tuesday, March 5, 2013

SQL Loader Control File

SQL Loader is a Oracle provided utility to load the data or records from any external systems.
It can load .csv(Comma seperated values), Pipe delimited values.

In Oracle apps, there's a executable type called "SQL* Loader"
This kind of concurrent programs search for control file in $APPL_TOP/1.0.0/bin

Sample control file

OPTIONS (ERRORS=30000)
OPTIONS(SKIP=1)
   LOAD DATA
   INFILE '%1' --to get the file name as parameter from the concurrent program
   APPEND -- it can also be INSERT to insert into fresh tables
   INTO TABLE APPS.SERIAL_TABLE
   FIELDS TERMINATED BY ','  --Pipe delimited value (, or |)
   OPTIONALLY ENCLOSED BY '"'
   TRAILING NULLCOLS
  (
        SERIAL_NUMBER,
        MAX_COUNT,
        EXPIRY_DATE         "TO_DATE(TRIM(:EXPIRY_DATE),'MM/DD/YYYY')",
        TEMP_NAME         "REPLACE(:TEMPLATE_NAME,'|',',')",
        TEM_ID,
        EXT_DATE FILLER , -- FILLER option can be used above SQL loader Version 8i.
        --EXT_DATE          "TO_DATE(TRIM(:EXTRACT_DATE), 'MM/DD/YYYY HH24:MI')",
        --EXT_DATE         "TO_DATE(TRIM(:EXTRACT_DATE), 'MM/DD/YYYY HH:MI:SS PM')",
        BATCH_ID "trim(trailing ' ' from LTRIM(RTRIM(:BATCH_ID)))",
        STATUS CONSTANT 'NEW',
        CREATION_DATE SYSDATE,
        LAST_UPDATE_DATE SYSDATE,
        CREATED_BY     "-1",
        LAST_UPDATED_BY "-1"
  )


To Run the Control file
sqlldr apps/apps_pwd control=control_file_name.ctl

Wednesday, January 30, 2013

Phases of a Concurrent Program

When we run concurrent programs in Oracle applications. It follows the below phases to complete a program
1) Pending
2)Running
3)Completed
4)InActive

There are more status for each phases. Find it below

Phase Status Description
PENDING Normal Request is waiting for the next available manager

Standby Program to run request is incompatible with other program(s) currently running

Scheduled Request is scheduled to start at a future time or date.

Waiting A child request is waiting for its Parent request to mark it ready to run. For example, a request in a request set that runs sequentially must wait for a prior request to complete
RUNNING Normal Request is running normally
Paused Parent request pauses for all its child requests to finish running. For example, a request set pauses for all requests in the set to complete.

Resuming All requests submitted by the same parent request have completed running. The Parent request resumes running.

Terminating Request is terminated by choosing the Cancel Request button in Requests window
COMPLETED Normal Request completed successfully.

Error Request failed to complete successfully

Warning Request completed with warnings. For example, a request is generated successfully but fails to print

Cancelled Pending or Inactive request is cancelled by choosing the Cancel Request button in the Requests window.

Terminated Request is terminated by choosing the Cancel Request button in the Requests window.
Inactive Disabled Program to run request is not enabled. Contact your system administrator.

On Hold Pending request is placed on hold by choosing the Hold Request button in the Requests window.

No Manager No manager is defined to run the request. Check with your system administrator. A status of No Manager is also given when all managers are locked by run-alone requests.

Tuesday, January 29, 2013

Query to find Request group name from Concurrent Program Name

Here's the query to find the request group name by passing concurrent program name as parameter

SELECT
    FRG.REQUEST_GROUP_NAME,
    FRG.DESCRIPTION,
    CP.CONCURRENT_PROGRAM_ID,
    CP.CONCURRENT_PROGRAM_NAME,
    CPT.USER_CONCURRENT_PROGRAM_NAME,
    FRV.RESPONSIBILITY_NAME
FROM
    FND_REQUEST_GROUPS FRG,
    FND_REQUEST_GROUP_UNITS FRGU,
    FND_CONCURRENT_PROGRAMS CP,
    FND_CONCURRENT_PROGRAMS_TL CPT,
    FND_RESPONSIBILITY_VL FRV
WHERE
    FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
    AND FRGU.REQUEST_UNIT_ID = CP.CONCURRENT_PROGRAM_ID
    AND CP.CONCURRENT_PROGRAM_ID = CPT.CONCURRENT_PROGRAM_ID
    AND FRV.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID(+)
AND CPT.USER_CONCURRENT_PROGRAM_NAME LIKE 'XX Conc Pgm Name';

Wednesday, November 28, 2012

Set up for OAF pages development

This post helps you in setting up the OAF page development environment.

Pre-Requisites to Setup OAF development environment
-------------------------------------------------------------------------------
1) download Jdeveloper OAF Patch for respective Oracle eBiz Instance.
2) download dbc files from unix server for respective Oracle eBiz Instance you are going to work with.

Detailed instructions
----------------------------------
1) Find your Oracle eBiz Version by logging into your apps -- Click About this page link at bottom of the page -- Click Technology components tab -- you will see the screen below



Note down your OA Framework version. In my case it is 12.1.3.

So i need to download the Jdeveloper OAF patch for this version(12.1.3) in the below link.
How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x (Doc ID 416708.1)

you will find the table like below shown



click the respective patch and download it.

Extract the downloaded .zip file into any location.
C:\p7523554_R12_GENERIC

you will find 3 folders. (jdevbin, jdevdoc, jdevhome)

Set the JDEV_USER_HOME environment variable
----------------------------------------------------
Right click my computer -> Properties -> Advanced Tab -> Environment variables



Variable name : JDEV_USER_HOME
Variable value will be DRIVE_LOCATION:\PATCH_NAME\jdevhome\jdev


You have almost completed the setup.

Downloading dbc(database connectivity file) 
-----------------------------------------------
This file used to connect the Jdeveloper to your Oracle eBiz Instance.
This file will be available with your DBA's or $FND_SECURE top.

FTP the .dbc file to "C:\p7523554_R12_GENERIC\jdevhome\jdev\dbc_files\secure"

dbc file contains information like below


#DB Settings
#Mon May 02 18:26:56 IST 2012
GUEST_USER_PWD=GUEST/ORACLE
APPL_SERVER_ID=A1E590D05E00456FE04400000000000021673182782189486806263446144422
FND_JDBC_BUFFER_DECAY_INTERVAL=300
APPS_JDBC_DRIVER_TYPE=THIN
FND_JDBC_BUFFER_MIN=1
GWYUID=APPLSYSPUB/PUB
FND_JDBC_BUFFER_MAX=5
APPS_JDBC_URL=
FND_JDBC_STMT_CACHE_SIZE=100
TWO_TASK=POC12
JDBC\:oracle.jdbc.maxCachedBufferSize=358400
JDBC\:processEscapes=true
FND_MAX_JDBC_CONNECTIONS=500
FND_JDBC_USABLE_CHECK=false
FNDNAM=APPS
FND_JDBC_PLSQL_RESET=false
DB_PORT=1553
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5


Now start your OAF page development.