Shell script

#!/bin/sh

ls -l *.xml|nl|awk '{print ""$10","$1",\x27"$10"\x27"}' > xml_files.dat

sqlldr username/password(for database)@localhost:1521/xe control='xml.ctl'

sqlplus -s username/password(for database)@localhost:1521/xe<<END
execute  xml_dataloader_p
delete from xml_files where file_name is not null;
Commit;
exit;
END
>xml_files.dat


ls -l ----> To display all directories.
nl ----> To display line numbers.
awk ----> awk formating text in required format in a file.

$10 ----> 10 th column of response from "  ls -l *.xml |nl "

[karunan@eappnodejs anand]$ ls -l *.xml|nl

1  -rw-rw-r--. 1 karunan karunan  8747 Jun 12 15:0noshipment1_2017_03_30_02_55_18_013.xml

count of above line like 1 for 1st and .xml file name is 10th 

$1 ---> sequence
\x27 ---> To display single code " ' "

http://www.unix.com/shell-programming-and-scripting/130984-how-call-oracle-stored-procedure-unix-shell.html

Downloading ldts through script :

#!/bin/sh
while read -r line;
do
FNDLOAD sqlusername/password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $line.ldt PROGRAM APPLICATION_SHORT_NAME="XXXX" CONCURRENT_PROGRAM_NAME="$line"
mv *.ldt /home/oracle/sampleldts/ldts
mv *.log /home/oracle/sampleldts/logs
done < file.txt
 

     

Loading xml elements from multiple xml files into oracle table.

How to transform xml data from multiple xml files into oracle database tables.

      1.       Extracting xml file names from the directory into .dat file. This .dat file will be referred

             by control file.

      2.       Processing xml files in the dat file through sql loader with the following command.

                   SQL >  host sqlldr control = ‘control file name.ctl’

      3.       We can querry the result set from the table “xml_files”.

            SQL>  SELECT     replace(file_name,'''','')

                                      ,    EXTRACTVALUE (xmldata, '//AirwayBillNumber/text()')

                                      ,     EXTRACTVALUE (xmldata, '//ShipperID/text()')

                            FROM     xml_files ;

       4.       Processing xml data elements from xml type table “xml_type” into base                                          table"xxrp_acct_header”.

        5.       Execute the following procedure “accountHEADER” to load required xml elements into              “xxrp_acct_header” with the following command and also empty the table “xml_files”

          exec accountHEADER;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The following syntax should be followed while extracting xml file names into .dat file. Filename “'xml_files.dat'”

                          "      Xmlfilename1.xml,1,’xmlfilename1.xml'

                                  Xmlfilename2.xml,2,’xmlfilename2.xml'

                                  Xmlfilename3.xml,3,’xmlfilename3.xml'    ”

The .dat file will be referenced by control file which locks as follows. File name “xml.ctl”

                           “          load data

                                       infile 'xml_files.dat'

                                       into table XML_FILES append

                                       fields terminated by ','

                                        (

                                        xml_filler filler char

                                        ,xmldata lobfile(xml_filler) terminated by EOF

                                         ,file_id

                                         ,file_name

                                         )               ”

Create the following tables must be created.

                             SQL>  create table XML_FILES(

                                         XMLDATA     sys.XMLTYPE        NOT NULL,

                                         FILE_ID          NUMBER (10)        NOT NULL,

                                         FILE_NAME  VARCHAR2 (255)   NOT NULL

                                         );

                              SQL> create table xxrp_acct_header (

                                         file_name                    varchar2(100),

                                         AirwayBillNumber     varchar2(100),

                                         ShipperID                    varchar2(100)

                                         );

Compile and Execute the following procedure.

“set SERVEROUTPUT ON

/

CREATE OR REPLACE PROCEDURE accountHEADER AS

BEGIN

insert into xxrp_acct_header (file_name,AirwayBillNumber,ShipperID)

SELECT replace(file_name,'''',''), EXTRACTVALUE (xmldata, '//AirwayBillNumber/text()'),

EXTRACTVALUE (xmldata, '//ShipperID/text()')

FROM xml_files ;

delete from xml_files where file_name is not null;

COMMIT;

END accountHEADER;

/

show errors

/

exec accountHEADER;

/”

SELECT XMLTYPE(t.response_xml).EXTRACT('//PieceNumber/text()').getStringVal() as PieceNumber
from  inv_shipment_requests t
where SERVICE_TYPE='SHIPMENT'
and   STATUS='Success'
and   AIRWAY_BILLNUMBER = 111;

OR

SELECT
EXTRACTVALUE(xmltype(t.response_xml), '//PieceNumber/text()') as PieceNumber
from  inv_shipment_requests t
where SERVICE_TYPE='SHIPMENT'
and   STATUS='Success'
and   AIRWAY_BILLNUMBER = 111;

Oracle Package using callable statement:

Oracle Package using callable statement:

Code for co:

                        try{

                              String rocid = "100023";  //pageContext.getParameter("LINE_ID");

                              String mponent = pageContext.getParameter("Name");

                              String lots = pageContext.getParameter("Number");

                             

                              OAFormValueBean item = (OAFormValueBean)webBean.findChildRecursive("Item");

                              String component_item_id = (String)item.getValue(pageContext);

                             

                              String qty = pageContext.getParameter("Quantaty");

                              Serializable pars[] = {cid,component,lot,totalserialnumber,item_id,qty};

                              String[] msgs=(String[])am.invokeMethod("methodname",pars);

                             

                              String succflag = null;

                              String msg = null;

                              

                              if(msgs.length>0){

                                  succflag = msgs[1];

                                  msg = msgs[2];

                                 

                                  if(msg!=null && msg.length()>0) {

                                        if(succflag.equals("Y")){

                                            OAException exception = new OAException(msg, OAException.INFORMATION);

                                            pageContext.putDialogMessage(exception);   

                                        }

                                        else{

                                            OAException exception = new OAException(msg, OAException.ERROR);

                                            pageContext.putDialogMessage(exception); 

                                        }

                                }

                            }

                        }

                        catch (Exception e) {

                             OAException.wrapperException(e);               

                        }

Code for AMIMPL:

    public String[] methodname(String p_procid, String p_componentid, String p_LotNum, String p_SerNum, String p_itemid, String p_qty){   

        int lv_procno = 0;

        int lv_itemid = 0;

        int lv_qty    = 0;

        String fg_alloc_id = null;

        String child_proc_id = null;

        int raw_alloc_id = -1;

           

        lv_procno = Integer.parseInt(p_procid);

        lv_itemid = Integer.parseInt(p_itemid);

        lv_qty   = Integer.parseInt(p_qty);

       

        StringBuffer str = new StringBuffer();

        str.append( " BEGIN ");

        str.append( " packagename.procedurename ( ");

        str.append( " proc_id => :1, ");

        str.append( " item => :2, ");

        str.append( "lot => :3, ");

        str.append( " numbers => :4, ");

        str.append( " item_id => :5, ");

        str.append( " qty => :6, ");

        str.append( " id => :7, ");

        str.append( " oc_id => :8, ");

        str.append( " xid => :9, ");

        str.append( " xflag    => :10, ");

        str.append( " xge => :11  ");

        str.append( " ); ");

        str.append( " END; ");

           

        OADBTransaction oa = (OADBTransaction)getTransaction();

        OracleCallableStatement cs = (OracleCallableStatement)oa.createCallableStatement(str.toString(), 1); 

       

        String msgs[]=new String[3];

        String SuccFlag;

        String ErrMsg;

        String rawalloc;

        try{

            cs.setInt(1,lv_procno );

            cs.setString(2,p_componentid);

            cs.setString(3,p_LotNum );

            cs.setString(4,p_SerNum);

            cs.setInt(5,lv_itemid );

            cs.setInt(6,lv_qty);

            cs.setString(7,fg_alloc_id );

            cs.setString(8,child_proc_id);

            cs.setInt(9,  raw_alloc_id );

           

            cs.registerOutParameter(9, Types.INTEGER);

            cs.registerOutParameter(10,Types.VARCHAR);

            cs.registerOutParameter(11,Types.VARCHAR);

           

            cs.execute();

            oa.commit();

           

            rawalloc = cs.getString(9).toString();

            SuccFlag = cs.getString(10);

            ErrMsg   = cs.getString(11);

           

            msgs[0]  = rawalloc;

            msgs[1]  = SuccFlag;

            msgs[2]  = ErrMsg;

        }  

        catch(Exception e){

            OAException.wrapperException(e);

        }

            return msgs;

    }

Multiple Checkbox and Concatenating select flag values

Multiple Checkbox:

• In the selected VO create a one transient variable like selected flag.
• In the table region we create one multiple selection
• Under the multiple selection assign the selected flag value using view instance and view attribute.

Concatenating using selected flag under selected values

            OAViewObject vo = (OAViewObject)am.findViewObject("Name of VO1");

           

            if(vo.isPreparedForExecution()){

                Row row[] = vo.getFilteredRows("SelectFlag","Y"); // Added for multiple select

               

                if(row.length < 1)  {

                       OAException exception = new OAException("Please Select atleast one serial number for reservation", OAException.WARNING);

                       pageContext.putDialogMessage(exception);  

                }

                else{

                        String totalserialnumber = "";

                        int j =0;

                        for (int i=0;i<row.length;i++){

                            String lvserialnumber = "";

                            Name of VORowImpl rowi = (Name of VORowImpl)row[i];

                           

                            if (rowi.getSelectFlag()!= null && rowi.getSelectFlag().equals("Y")){

                                j=j+1;

                                if(rowi.getSerialNumber()!=null && rowi.getSerialNumber().length()>0 ){

                                    lvserialnumber =rowi.getSerialNumber();

                                    }                          

                                if(lvserialnumber != null && lvserialnumber.length()>0 && j==1){

                                     totalserialnumber = lvserialnumber;

                                     }

                                else if(lvserialnumber != null && lvserialnumber.length()>0 && j>1){

                                     totalserialnumber = totalserialnumber+"|"+lvserialnumber;

                                } 

                                                 

                            }

                        }

Clear And Cancel Buttons

Handling on Clear Button:

Code for CO

Take the Item style to button and Action type fire action and event clear

if("clear".equals(pageContext.getParameter(EVENT_PARAM))){

OAMessageLovInputBean OAitem=(OAMessageLovInputBean)webBean.findChildRecursive("ItemName");

 OAitem.setValue(pageContext,null);

             

OAMessageTextInputBean omtb = (OAMessageTextInputBean)webBean.findChildRecursive("Quantaty");

omtb.setValue(pageContext,null);

             

 }

  

Cancel button:

Code for Co:

 using url and passing parameter to get main page

  if ("Cancel".equals(pageContext.getParameter(EVENT_PARAM))){

         HashMap map = new HashMap();

         String pProcessId = pageContext.getParameter("PrcId");

         map.put("rcId", ProcessId);

         pageContext.setForwardURL("OA.jsp? page=/xxxx/oracle/apps/fnd/test/webui/namePG",

                                                  null,

                                                  OAWebBeanConstants.KEEP_MENU_CONTEXT,

                                                  null,

                                                  map,

                                                  false,

                                                  OAWebBeanConstants.ADD_BREAD_CRUMB_NO,

                                                  OAWebBeanConstants.IGNORE_MESSAGES); 

                     }

Creating form value and Search button code

Form value:

• Form values assign only used in lovmappings
• In the messagelayoutlovinputregion item under column take form value under the form value mapping return item and criteria item giving to another form value name.
• Passing parameters using this code.

Under this code write only co;

             

OAFormValueBean itemfv = (OAFormValueBean)webBean.findChildRecursive("ItemIDFV");

String itemno = (String)itemfv.getValue(pageContext);

Search Button:

Code For CO:

if(pageContext.getParameter("searchId")!=null){

    try{

OAFormValueBean itemfv = (OAFormValueBean)webBean.findChildRecursive("ItemIDFV");

          String itemno = (String)itemfv.getValue(pageContext);

      

          String lotnumber =  pageContext.getParameter("LOtNumber");

          String serialstart = pageContext.getParameter("serialstart");

          String serialend = pageContext.getParameter("serialend");

              

          Serializable param[] = {itemno,lotnumber,serialstart,serialend};

          am.invokeMethod("initdetails",param);

         }

         catch (Exception e) {

             OAException.wrapperException(e);

          }

      }

AMIMPL code:

    public void initdetails(String itemid, String lotnumber ,String serialstart , String serialend){

        VOImpl vo = getVO1();

        try{

            vo.clearCache();

            vo.setWhereClause(null);

            vo.setWhereClauseParam(0,itemid);

            vo.setWhereClauseParam(2,lotnumber);

            vo.setWhereClauseParam(3,serialstart);

            vo.setWhereClauseParam(4,serialend);

            vo.executeQuery();

        }

        catch(Exception e){

            throw OAException.wrapperException(e);

         }

    }

OAF LOV Dependence

Lov Dependences

              MainRN --           Id : MainRN

                                       Region Style : pageLayout

                                       AM Definiton: Add that page location

                                       Windows title & title : same to display the page and load url

             MessageLayoutRN --        Id : MessageLayoutRN

                                                    Region Style : Message Component Layout

• Right click on message layout region and select “Message layout lov input region”
• We have to take two message layout lov input regions
• If you want attribute from out side take one vo and the querry is created to asking the attribute like (select empno from emp where empno = :1).
• Then you take one form value and assign the outside attribute to created form value using view instance and view attribute.
• In the message layout lov input region under one item
• In the item under one region and one lovmappings
• Region using table region wizard and getting the columns in table using vo
• Lovmapping based on columns take love maps 
• Each map lov region item assign the column name
• First map assign return item and criteria item giving to main item name.
• Second and remaing maps only giving to criteria item only.
• In the second message layout lov input region giving to same but the second lovmapping criteria item giving to first item colum based form valu name.

Code for co:

        if("ItemName".equals(pageContext.getLovInputSourceId())){ 

                      am.invokeMethod("xxipGetItems");

                  }

Code for AMIMPL:

    public void xxxGetItems(){

        LOVVOImpl vo = getLOVVO1();

        try{

            vo.clearCache();

            vo.setWhereClause(null);

            vo.setWhereClauseParams(null);

            vo.executeQuery();

        }

        catch(Exception e){

            throw OAException.wrapperException(e);

         }

    }

Oracle Apps Data Migration

Oracle Apps Create ctl, dat, and concurrent program and run.

1.Giving to md40 document this document need client required table column’s and data type is there.

2.Create a cs v (XL) file to all client needed column in heading and fill the data. Mainly I mentioned data header and data footer and middle data body we create.

3.All the XL file data copy to text document without column’s and replace the space to ‘|’ mark in text document.

4.This text document save to DAT file.

5.After that we create a staging table and also create control file. Then table will execute sql plus prompt.

6.And we have to copy the control file, DAT file to winscp.

7.Then finally we create a concurrent program in apps and run the concurrent program.

8.Finally we create a package and run that package.

Oaf Page deployment

Steps to Deploy OAF page into APPS
---------------------------------------

1.) In Jdeveloper compile the page.
    When page got compiled, it will create .class files for all our java files.
    java files and xml files ->  Jdeveloper installed place\jdevhome\jdev\myprojects
    class files              ->  Jdeveloper installed place\jdevhome\jdev\myclasses
 
2.) Java Files can also be compiled in UNIX.
    Go to JAVA_TOP(/u01/oracle/DEMO/apps/apps_st/comn/java/classes).

3.) In JAVA_TOP copy your package structure of myprojects (eg:  xxxx/oracle/apps/per/selfservice/server, xxxx/oracle/apps/per/selfservice/webui)

4.)Compile each Java file.
   eg. xxxx/oracle/apps/per/selfservice/server
 
        xx_paymentsVOImpl.java
     
        javac xx_paymentsVOImpl.java  -> To compile java file.

5.)Continue this process for all java files.

6.) Run your import script for pages and regions.

7.)Import script should be run in webui(xxxx/oracle/apps/per/selfservice/webui)

8.)Go to xxxx/oracle/apps/per/selfservice/webui in UNIX
   and run below Import script.
 
   java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxxx/oracle/apps/per/selfservice/common/webui/PayslipPG.xml -username apps -password appsdemo -dbconnection "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host address)(PORT=portno)))(CONNECT_DATA=(SID=DEMO)))" -rootdir $JAVA_TOP

 
9.)Import scrip can also be run from our cmnd prompt, check it in Google once.

10.) Print Documents to check imported XML page content (Optional)

     Example –

     DECLARE

     BEGIN

     jdr_utils.printDocument('/prajkumar/oracle/apps/fnd/webui/HelloWorldPG',1000);

     EXCEPTION

     WHEN OTHERS THEN

     DBMS_OUTPUT.PUT_LINE(SQLERRM);

     END;
   
11.) Create a Function(Application Developer->Applicaton->Function)
     In Properties -> select Type      = SSWA JSP Function
     In WEBHTMl    -> select HTML Call = OA.jsp?page=/xxxxx/oracle/apps/per/selfservice/common/webui/PayslipPG
   
12.) Create a Menu and submenu.Attach this Function to sub menu and submenu to menu.
     Attach menu to Responsibilty and run the page.


PagePath: XXSpel/oracle/apps/po/requisition/webui/XXSpelPG.xml


RespName: XXEppsysPracticeResp

Menu : OAFPAGES

Employee Data   Employee Data Function
DbOafPages      OAFForm

XXEMPLOYEEINFO

OA.jsp?page=XXSpel/oracle/apps/po/requisition/webui/XXSpelPG


OA.jsp?page=XXGetEMPData/oracle/apps/po/EmpData/webui/XXEmpDataPG.xml


java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/XXGetEMPData/oracle/apps/po/EmpData/webui/XXEmpDataPG.xml - username apps -password appsdemo -dbconnection "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host address)(PORT=portno)))(CONNECT_DATA=(SID=DEMO)))" -rootdir $JAVA_TOP


Employee Data Function



Server bouncing

Bounce the server:
====================

Go to the custom path where class file is present

run adcgnjar

Bounce the oacore

cd $ADMIN_SCRIPTS_HOME
/*
./admanagedsrvctl.sh stop oacore_server1

./admanagedsrvctl.sh start oacore_server1

*/

Bounce the apache

./adapcctl.sh stop

./adapcctl.sh start