27-03-2019

                                                                  Apex Project Creation

1. Create new project and select desktop application then click next button.
2. Creation application select the below fileds.
      user interface  : Desktop
      schema            : XXIP
      Name               : project name
      Application      : 104
      Theme              : select your interested themes

If you need then select Theme style.

3. Add the new page from the existing main page. if you need any shared components to import this project then select yes radio button otherwise no. Finally application was created.

4. In Home page create one region and select type is classic report and write a query in the sql block.

SELECT DISTINCT PROPOSAL_TYPES as "Proposal Types",PROPOSAL_TYPE_ID as "Proposal Codes",
apex_item.checkbox(1,PROPOSAL_TYPES)"Check"
FROM XXMAP_PROPOSAL_QUESTIONS

5. Select column level check column and go to the security field and then select for "NO" Escape the special character for check box.

6. If you need column level also check box then enter the Header place enter the below code.

heading  =>  <input type = "checkbox"onclick="$f_CheckFirstColumn(this)">

7. Create the button under the region then type the button name and title and also select the button position(Top and bottom of the region).

8. Then create a page processing under create a processes. write the plsql code for the relevant block.
then move to the server side condition then select the button.

/

Login page and security phase authentication

1. To upload the company logo and backend images to the share point
    go to the share point -> files -> static workspace files
    to upload a file and take the reference characters.
2.Go to the login page under filter properties -> css -> Inline  paste the css code

3. select the content body region and enter the title place below code.

<h style="font-size:35px;font-family:Cooper;color:white;"> Clinigen Mapping Proposal</h>

In title logo if you want to display null then use below syntax

<span style="display:none">background name </span>

4. select the user name filed and go to the appearance filed in filtered option select the value placeholder column update the required water mark.

5. if you want to disable the login symbol then go to the appearance field in content body region select the template option  and uncheck the use template default. then go to the region select content body and go to the appearance and empty the icon css classes

6. security authentication go to the sharedcomponents  and select the security -> Authentication schema then create the authentication. to use below function in custom

Function xx_login_fn(p_username IN varchar2
                    ,p_password IN varchar2
        )
return boolean
As
xx number := 0;
Begin
select 1 into xx
from   XXMAP_USERS
where  upper(USER_NAME) = upper(p_username)
and    PASSWORD         = p_password;
return true;
Exception
when NO_DATA_FOUND THEN
Return FALSE;
end;

/////

7. After create process button go to the next page below are the changes are required

Create a branches _> Exicute options == point = After processing
                                  Behavior            --- type   = page or url

Oracle Apex Login Page Design

Go To Page 101 Login Page -> Filter Properties

CSS -> Inline

This code Display Company logo.

span.t-Login-logo {
  background-image: url(#APP_IMAGES#logo_dark.png);
  background-size: cover;
  width: 665px;
  height:120px;
}

This code Display Background Body.

.t-PageBody--login .t-Body{
    background: url(#APP_IMAGES#pexels-photo-248797.jpeg);
 background-repeat: no-repeat;
 background-size: cover;

}

This code Adjustment Of the Body. 

.t-Login-region {
     position: relative;
 widht: 410px;
 margin: 0 auto;
 background: rgba(130,130,130,.3);
 padding: 20px 22px;
 border: 1px solid;
 border-top-color: rgba(255,255,255,.4);
 border-left-color: rgba(255,255,255,.4);
 border-bottom-color: rgba(60,60,60,.4);
 border-right-color: rgba(60,60,60,.4);
}

You have to disable the login icon.

• Go to Page items.
• Select Region.
• Under the Region go to Apearance disable the "Template Options".

If you want water mark simbols like "Enter Username".

• Select P101-USERNAME label.
• Goto Filter Properties. 
• Appearance select 'Value PlaceHolder' then enter the text.

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

                     }