Dynamic EO and VO Creation in OAF

Call the below methods in your PR to create dynamic EO and VO.

//Method  to create an EO dynamically.
============================
    public EntityDefImpl dynamicEO(OAPageContext pageContext,
                                   OAWebBean webBean, String baseTable) {
        UtilCO utilCO = new UtilCO();
        AttributeDefImpl newAttrDef1 = null;
        OAViewRowImpl rowImpl = null;
        String attributeName = null;
        String columnName = null;
        String dataType = null;
        String tableName = null;
        Class javaType = null;
        int tabColRow = 0;
        OAApplicationModuleImpl am =
            (OAApplicationModuleImpl)pageContext.getApplicationModule(webBean);
        OAViewObject tabColVO =
            (OAViewObject)am.findViewObject("tableColumnsVO1");
        tabColVO.setWhereClause(null);
        tabColVO.setWhereClause("table_name = '" + baseTable + "'");
        tabColVO.executeQuery();
        tableName = utilCO.caseConvert(baseTable);
        EntityDefImpl newEntity = new EntityDefImpl(tableName + "EO");
        newEntity.setSource(baseTable); //Table Name
        newEntity.setFullName(this.getClass().getPackage().getName() + "." +
                              tableName + "EO"); //packagename.eoName
        tabColRow = tabColVO.getRowCount();
        try {
            RowSetIterator rowIter = tabColVO.createRowSetIterator("rowIter");
            rowIter.setRangeStart(0);
            rowIter.setRangeSize(tabColRow);
            for (int i = 0; i < tabColRow; i++) {
                rowImpl = (OAViewRowImpl)rowIter.getRowAtRangeIndex(i);
                if (utilCO.notNull(rowImpl.getAttribute("ColumnName")) &&
                    utilCO.notNull(rowImpl.getAttribute("DataType"))) {
                    columnName = rowImpl.getAttribute("ColumnName").toString();
                    dataType = rowImpl.getAttribute("DataType").toString();
                    attributeName =
                            utilCO.caseConvert(columnName); //ColumnName
                } else {
                    throw new OAException("Exception when creating dynamic EO.");
                }
                javaType = getJavaType(dataType);

                //  addAttribute(java.lang.String attrName, java.lang.String columnName,
                //             java.lang.Class javaType,  boolean isPrimaryKey,
                //             boolean isDiscriminator,   boolean isPersistent
                //            )
                newAttrDef1 =
                        newEntity.addAttribute(attributeName, columnName, javaType,
                                               false, false, true);
                //Setting Who Column values by default
                if ("CREATION_DATE".equals(columnName) ||
                    "LAST_UPDATE_DATE".equals(columnName)) {
                    newAttrDef1.setDefaultValue(pageContext.getCurrentDBDate());
                } else if ("CREATED_BY".equals(columnName) ||
                           "LAST_UPDATED_BY".equals(columnName)) {
                    newAttrDef1.setDefaultValue(pageContext.getUserId());
                } else if ("LAST_UPDATE_LOGIN".equals(columnName)) {
                    newAttrDef1.setDefaultValue(pageContext.getLoginId());
                }

            }
            rowIter.closeRowSetIterator();
        } catch (Exception e) {
            throw new OAException("Exception in rowIterate Method: " +
                                  e.toString(), OAException.ERROR);
        }
        newAttrDef1 =
                newEntity.addAttribute("RowID", "rowid", RowID.class, true,
                                       false, true);
        //newAttrDef1.setPrimaryKey(true);
        newEntity.resolveDefObject();
        newEntity.registerDefObject();
        if (am.findViewObject(tableName + "VO") != null) {
            System.out.println("VO Already exists. Skipping creation.");
        } else {
            am.createViewObject(tableName + "VO",
                                createEOBasedVO(newEntity, tableName));
        }
        return newEntity;
    }


    public Class getJavaType(String dataType) {
        Class javaType = null;

        if ("VARCHAR2".equals(dataType.toUpperCase())) {
            javaType = String.class;
        } else if ("NUMBER".equals(dataType.toUpperCase())) {
            javaType = Number.class;
        } else if ("DATE".equals(dataType.toUpperCase())) {
            javaType = Date.class;
        } else if ("BLOB".equals(dataType.toUpperCase())) {
            javaType = BlobDomain.class;
        } else if ("CLOB".equals(dataType.toUpperCase())) {
            javaType = ClobDomain.class;
        } else {
            throw new OAException("Unknow Data Type found..." + dataType);
        }

        return javaType;
    }


//Dynamic Vo Based on EO:
======================
    public ViewDefImpl createEOBasedVO(EntityDefImpl eoImpl,
                                       String tableName) {
        ViewDefImpl newView = new ViewDefImpl(tableName + "VO");
        this.getClass().getPackage();
        newView.setFullName(this.getClass().getPackage().getName() + "." +
                            tableName + "VO");
        newView.addEntityUsage("e", eoImpl.getFullName(), false, false);
        newView.addAllEntityAttributes("e");
        newView.setFetchSize((short)30);
        newView.setComponentClass(null);//Since ComponentClass is set as null the vo created is of type Oracle.jbo.viewObject.
//This causes problem when assigning the vo to a bean or when getting handle of the bean. To avoid this, pass
//class OAViewObjectImpl. This will change the viewobject class to OAViewObject.
        newView.setRowClass(null);
        newView.setSelectClauseFlags(ViewDefImpl.CLAUSE_GENERATE_RT);
        newView.setWhereClauseFlags(ViewDefImpl.CLAUSE_GENERATE_RT);
        newView.setFromClauseFlags(ViewDefImpl.CLAUSE_GENERATE_RT);
        newView.resolveDefObject();
        newView.registerDefObject();
        return newView;
    }

Sending Multiple Notification Using ADHOCROLE

1.Create a Attribute(XX_APPROVER) of type 'Role'.
2.Create a Notification and set the Performer as XX_APPROVER.
3.Create a ADHOC user Group
V_USER_CONCAT :='user1 user2 user3';
XX_FYI_ROLE : ='XX_FYI_ROLE';
      WF_DIRECTORY.CREATEADHOCROLE
          (
         ROLE_NAME                             => XX_FYI_ROLE
          ,role_display_name                     => XX_FYI_ROLE
          ,LANGUAGE                              => NULL
          ,territory                                      => NULL
          ,role_description                         => 'MAIN_ROLE'
          ,notification_preference             => 'MAILHTML'
          ,role_users                                  => NULL
          ,email_address                            => NULL
          ,fax                                               => NULL
          ,STATUS                                     => 'ACTIVE'
          ,PARENT_ORIG_SYSTEM      => 'WF_LOCAL_ROLES'
          ,OWNER_TAG                           => NULL
);


4.Add User to the role
      WF_DIRECTORY.ADDUSERSTOADHOCROLE(
                                       ROLE_NAME      =>XX_FYI_ROLE,
                                       ROLE_USERS     =>V_USER_CONCAT
                                       );


5.Attach the Role to the attribute(XX_APPROVER)

         WF_ENGINE.SETITEMATTRTEXT (
                                    ITEMTYPE     => ITEMTYPE,
                                    itemkey            => itemkey,
                                    ANAME         => 'XX_APPROVER',
                                    AVALUE        =>  XX_FYI_ROLE 
                                   ); 
To Delete a User From User Group :

          WF_DIRECTORY.RemoveUsersFromAdHocRole(XX_FYI_ROLE,
  V_USER_CONCAT
  );
Query to Get all the USers attached to a Role :
select user_name from wf_local_user_roles where role_name like 'XX_FYI_ROLE';

Multiple Attachments in Notifications(In Table Format)

Multiple Attachments in Notifications(In Table Format)

Step1 :
Create a Document Type Item Attribute.

Step 2 :
Drag and Drop the item attribute in the Message you want to display the attachments.

Step 3 :
In your Message body Select HTML Body and paste give the attribute internal name

Step 4 :
Create a Procedure get_docs_in_notification.
create or replace
Procedure get_docs_in_notification( document_id IN VARCHAR2
,display_type IN VARCHAR2
,document IN OUT VARCHAR2
,document_type IN OUT VARCHAR2
)
IS
V_SAMPLE1 VARCHAR2(1000);
v_document_id varchar2(1000);
BEGIN
v_document_id :=document_id;
SELECT FND_GFM.CONSTRUCT_DOWNLOAD_URL
(FND_WEB_CONFIG.GFM_AGENT,document_id)
INTO V_SAMPLE1
FROM DUAL;
IF DISPLAY_TYPE = 'text/html' THEN
DOCUMENT :='<STYLE TYPE="text/css">';
DOCUMENT := DOCUMENT || 'TD{font-family: Arial; font-size: 10pt;}';
DOCUMENT := DOCUMENT || 'TH{font-family: Arial; font-size: 12pt;}';
document := DOCUMENT || '</STYLE>';
document := DOCUMENT || '<table border=1>';
DOCUMENT := DOCUMENT || '<tr>';
DOCUMENT := DOCUMENT || '<th colspan="2">Attachments</th>';
DOCUMENT := DOCUMENT || '</tr>';
FOR ILOOP IN (SELECT ROWNUM,
FDV.TITLE,
FND_GFM.CONSTRUCT_DOWNLOAD_URL
(FND_WEB_CONFIG.GFM_AGENT,fdv.media_id) as f_media_id
FROM FND_ATTACHED_DOCUMENTS FAD,
FND_DOCUMENT_ENTITIES FDE,
fnd_documents_vl fdv
WHERE FAD.ENTITY_NAME = FDE.DATA_OBJECT_CODE
AND FDE.DATA_OBJECT_CODE = 'XX_PNK_FORUM_ATTACHMENT'
AND FDV.DOCUMENT_ID = FAD.DOCUMENT_ID
AND fad.pk1_value = v_document_id
) LOOP
document := document || '<tr>';
DOCUMENT := DOCUMENT || '<td>';
DOCUMENT := DOCUMENT || ILOOP.ROWNUM;
document := document || '</td>';
document := document || '<td>';
DOCUMENT := DOCUMENT || '<a href = "';
DOCUMENT := DOCUMENT || ILOOP.F_MEDIA_ID;
DOCUMENT := DOCUMENT || '">';
DOCUMENT := DOCUMENT || ILOOP.TITLE;
document := document || '</a>';
document := document || '</td>';
document := document || '</tr>';
End Loop;
document := document || '</table>';
End If;
document_type := display_type;
END get_docs_in_notification;

Step 5 :
Now Call this procedure when setting value for your document through Pl/SQL.
WF_ENGINE.SETITEMATTRDOCUMENT(
L_ITEMTYPE,
L_ITEMKEY,
'DOC_AS_TABLE',
'PLSQL:get_docs_in_notification/' || 1014
);

The Output Will Be like :

Adding Multiple Attachments Dynamically

Adding Multiple Attachments Dynamically
Step 1 :
Create a Item attribute of type Document.

Step 2 :
Create a message attribute in your message where you want the attachment to appear. Give the internal
name of the attribute as “#ATTACHMENTS”.

Step 3 :
Now you can either give the value to the item attribute created in step 1 dynamically in query or can
hardcore it by inputting the value :
FND:entity=XX_PNK_FORUM_ATTACHMENT&pk1name=FORUM_ID&pk1value=1014
Where :
• XX_PNK_FORUM_ATTACHMENT is the entity name
• pk1name is the Primary Key 1 column Name obtained from table
FND_DOCUMENT_ENTITIES=> pk1_column
Use the Query :
SELECT FDE.DATA_OBJECT_CODE,
fde.pk1_column,
fad.pk1_value
FROM FND_ATTACHED_DOCUMENTS FAD,
FND_DOCUMENT_ENTITIES FDE
WHERE fad.entity_name = FDE.DATA_OBJECT_CODE
Code in Query:
Wf_Engine.setitemattrdocument
( l_itemtype
, l_itemkey
, 'ATTACHMENT_LINK'
, 'FND:entity=XX_PNK_FORUM_ATTACHMENT&pk1name=FORUM_ID&pk1value='||
l_primary_key
);
With this you can get multiple attachments in your notification.

Callable Statement in OAF

To Call a Procedure :

        OADBTransactionImpl txn =
            (OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction();

        CallableStatement cs =
            txn.createCallableStatement("begin :1 := check_Approval_Status(:2,:3); end;",
                                        OADBTransaction.DEFAULT);

        try {
            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setString(1, "retStatus");
            cs.setInt(2, scoreCardId);
            cs.setInt(3, personId);
            String outParamValue = null;
            cs.execute();
            outParamValue = cs.getString(1);
            cs.close();
            if (outParamValue.equals("N")) {
                OASubmitButtonBean oas =
                    (OASubmitButtonBean)webBean.findChildRecursive("MgrTransfer");
                oas.setDisabled(true);
            }

        } catch (SQLException sqle) {
            throw new OAException("Error in Staffing Query",
                                  OAException.ERROR);
        }

Example 2 :

OADBTransactionImpl OADBTxn =
                                (OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction();
                            String query_String = new String();
                            query_String =
                                    "BEGIN insert_into_table(:1,:2,:3,:4,:5);END;";

                            OracleCallableStatement stmt =
                                (OracleCallableStatement)OADBTxn.createCallableStatement(query_String,
                                                                                         -1);
                            try {
                                stmt.setInt(1, jObjectiveId);
                                stmt.setString(2, jName);
                                stmt.setInt(3, jScorecardId);
                                stmt.setString(4, jGroupCode);
                                stmt.setInt(5, jOwningPersonId);
                                stmt.execute();
                                stmt.close();
                            } catch (SQLException e) {
                    throw new OAException("Error in Staffing Query : " +
                                          e, OAException.ERROR);
                            }


To call a Function 

         OADBTransactionImpl txn = (OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction();
         
              CallableStatement cs =
         
                txn.createCallableStatement("begin :1 := xx_pra_func(:2); end;",OADBTransaction.DEFAULT);
         
              try {
         
                cs.registerOutParameter(1, Types.VARCHAR);
         
                cs.setString(1, "ValuesI");
                  cs.setInt(2, 100);
         
                String outParamValue = null;
         
                cs.execute();
         
                outParamValue = cs.getString(1);
         
                cs.close();
                  throw new OAException("Function us "+outParamValue);
         
              } catch (SQLException sqle) {
                  throw new OAException("Error in Staffing Query", OAException.ERROR);
              }

Prepared Statment in OAF

 OADBTransactionImpl OADBTxn =
                                (OADBTransactionImpl)pageContext.getApplicationModule(webBean).getOADBTransaction();
                try {
                    Connection conn =
                        pageContext.getApplicationModule(webBean).getOADBTransaction().getJdbcConnection();
                    String Query =
                        "select nvl((SELECT approval_status from apps.XX_SAMPLE_TABLE" +
                        " where objective_id=:1),'Approved') status from dual";
                    PreparedStatement stmt = conn.prepareStatement(Query);
                    stmt.setInt(1, objectiveId);
                    for (ResultSet resultset = stmt.executeQuery();
                         resultset.next(); ) {
                        pageContext.writeDiagnostics(this, "Query Executed",
                                                     1);
                        String result = resultset.getString("status");//Get the result of the query and store in the string result
                    }
                } catch (Exception exception) {
                    throw new OAException("Error in Staffing Query" +
                                          exception, OAException.ERROR);
                }

Performing Row Iteration in Contoller

   
  OAApplicationModule am1;
        OAWebBean oawebbean1 =
            webBean.findIndexedChildRecursive("ScorecardObjectivesRN");
        am1 = pageContext.getApplicationModule(oawebbean1);
        OAViewObject ScorecardObjectivesVO;
        ScorecardObjectivesVO =
                (OAViewObject)am1.findViewObject("ScorecardObjectivesVO");
        Row row[] = ScorecardObjectivesVO.getAllRowsInRange();
        for (int i = 0; i < row.length; i++) {
            ScorecardObjectivesVORowImpl sbrow =
                (ScorecardObjectivesVORowImpl)row[i];
            String groupCode = sbrow.getGroupCode();
            if (groupCode.equals("BO")) {
//Enter Your Code
            } else {
//Enter Else Part
            }

        }