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
            }

        }

How to call a VO in another AM in same page from controller

Suppose you want to access an VO in Your Page which is attached to a particular Region of the page and not to the Main AM, you can get the VO using the following Code :


        OAApplicationModule am1;
        OAWebBean oawebbean1 =
            webBean.findIndexedChildRecursive("mysampleRN");//Identify the Region to which your AM is attached. In this case mysampleRN is the Region.
        am1 = pageContext.getApplicationModule(oawebbean1);
        OAViewObject mysampleVO;
         mysampleVO  =
                (OAViewObject)am1.findViewObject(" mysampleVO");

Accessing a VO From Controller

OAApplicationModule am = 

                    pageContext.getApplicationModule(webBean);

                OAViewObject mySampleVO;

                if ((OAViewObject)am.findViewObject(" mySampleVO ") != 

                    null) {

                     mySampleVO = 

                            (OAViewObject)am.findViewObject(" mySampleVO ");

}