Attachments API - FND_DOCUMENTS_PKG, FND_ATTACHED_DOCUMENTS_PKG

DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE:=<Primary Key information that uniquely identifies the product (such as the Org Id / product_ID)>;
l_description fnd_documents_tl.description%TYPE:='Test Attachment';
l_filename VARCHAR2(240) := '<File Name>';
l_seq_num NUMBER;
l_blob_data BLOB;
l_blob BLOB;
l_bfile BFILE;
l_byte NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
x_blob BLOB;
fils BFILE;
blob_length INTEGER;
l_entity_name VARCHAR2(100) := <entity_name>; -- 'MTL_SYSTEM_ITEM' ;
l_category_name VARCHAR2(100) := <category_name>; -- 'Miscellaneous' ;

BEGIN

fnd_global.apps_initialize (<userid>, <applid>,<appluserid>);

SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;

SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;


-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name = <user_name>;

-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE NAME = 'FILE'
               AND  SOURCE_LANG = USERENV ('LANG');;

-- Select Category id for Attachments
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE USER_NAME = l_category_name;

-- Select nexvalues of document id, attached document id and
-- l_media_id
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL
--apps.fnd_documents_long_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id
--l_media_id
FROM DUAL;


SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;

fils := BFILENAME (<FLIE PATH>, l_filename); --BFILENAME ('/usr/tmp', l_filename);

-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.

INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, upload_date,
expiration_date, program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
)
VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf
SYSDATE,
NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,
'US', 'UTF8', 'binary'
)
RETURNING file_data
INTO x_blob;

-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;


-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_datatype_id => l_short_datatype_id, -- FILE
X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,
x_publish_flag => 'N', --This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 1,
x_usage_type => 'S',
x_language => 'US',
x_description => l_filename,--l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);

commit;



fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_language => 'US',
x_description => l_filename--l_description
);
COMMIT;
fnd_attached_documents_pkg.insert_row
(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),
x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),
x_seq_num => l_seq_num,
x_entity_name => l_entity_name,
x_column1 => NULL,
x_pk1_value => l_pk1_value,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => 6,
x_category_id => l_category_id,
x_security_type => 1,
X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,
x_publish_flag => 'Y',
x_language => 'US',
x_description => l_filename,--l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);
COMMIT;
DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);
END;
/

Execute parameterized PL SQL procedure from OAF page

import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;
import oracle.jdbc.OracleCallableStatement;
import java.sql.Types;
import oracle.apps.fnd.framework.OAException;
...

public String dataSumAction(String item1,String item2)
{ OADBTransaction oadbtransaction = (OADBTransaction)getTransaction();   OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getTransaction();

 String retValues;

 StringBuffer str = new StringBuffer();
 str.append( " BEGIN ");
 str.append( " test_package.data_sum( ");
 str.append( "       item1           => :1, ");
 str.append( "       item2           => :2, ");  str.append( "       data_sum    => :3  ");
 str.append( "    ); ");
 str.append( " END; ");

 OracleCallableStatement oraclecallablestatement =
  (OracleCallableStatement)oadbtransaction.createCallableStatement(str.toString(), 1);

 try{
  oraclecallablestatement.setInt(1,  Integer.parseInt(item1) );
  oraclecallablestatement.setInt(2,  Integer.parseInt(item2) );

  oraclecallablestatement.registerOutParameter(3, Types.VARCHAR);

  oraclecallablestatement.execute();
                   
  retValues = oraclecallablestatement.getString(3);
 }  catch(Exception e)
 {
  throw OAException.wrapperException(e);
 }
 return retValues;
}

========================================================================

 public void testStoredProcedure() { 

  

     OADBTransaction txn = getDBTransaction();  

     CallableStatement callableStatement =txn.createCallableStatement("begin mmm_procedure(:1, :2); end;",OADBTransaction.DEFAULT);  

     try 

      {  

       callableStatement.registerOutParameter(2, Types.VARCHAR);  

       callableStatement.setString(1, "mahi");  

       String outParamValue = null;  

       callableStatement.execute();  

       outParamValue = callableStatement.getString(1);  

       callableStatement.close();  

     } 

     catch (SQLException sqle) 

     {  

       callableStatement.close(); 

     } 

   } 

Launch Workflow from OAF Page

Using class oracle.apps.fnd.framework.webui.OANavigation provided by Oracle for Workflow API, you can launch workflow directly from OAF Page.  There are two ways to launch workflow :

1.  Oracle PL/SQL API (wf_engine)
2.  Java Wrappers

we shall launch a workflow from OAF page.

1.  Create a submit button and name the partial event as "launchWF"
2.  Write below java procedure to invoke workflow

import oracle.apps.fnd.framework.webui.OANavigation;

    public void launchWF(OAPageContext pageContext)
    {
        String PItemType = "XXTEST";
        String PProcess = "XXTEST_PROCESS";
        String PItemKey = "SEQUENCE VALUE";  // This can be a random item key generated
   
        OANavigation wf = new OANavigation();
   
        // Now create Workflow Process
        wf.createProcess(pageContext, PItemType, PProcess, PItemKey);
   
        // Set Employee number
        wf.setItemAttrText(pageContext, PItemType, PItemKey,"EMPLOYEE_ID", "101");
   
        // Start Workflow Process
        wf.startProcess(pageContext, PItemType, PProcess, PItemKey);
    }


3.  Call the above procedure in ProcessFormRequest Method

    public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
    {
    super.processFormRequest(pageContext, webBean);
    if ("launchWF".equals(pageContext.getParameter(EVENT_PARAM)))
      {
        launchWF(pageContext);
      }
    }

Passing parameters from one OAF page to another OAF Page

//In  processFormRequest()
HashMap xxhashMap = new HashMap(1);
xxhashMap .put("ParamName1", "Value1");

oapagecontext.setForwardURL("MY_FUNCTION", (byte)0, null, xxhashMap , true, "N", (byte)0);

OR

oapagecontext.setForwardURL("OA.jsp?page=/xxelng/oracle/apps/per/hiring/webui/XXConfermationPG",
                                      null,
                                      OAWebBeanConstants.KEEP_MENU_CONTEXT,
                                      null, xxhashMap , true,
                                      OAWebBeanConstants.ADD_BREAD_CRUMB_NO,
                                      OAWebBeanConstants.IGNORE_MESSAGES);

You can then retrieve this parameter in processRequest() via:
oapagecontext.getParameter("ParamName1");

Set DFF Segment Required in OA Framework

In processRequest or processFormRequest Use this code.....

OADescriptiveFlexBean oaDFF = (OADescriptiveFlexBean)webBean.findIndexedChildRecursive("FlexField"); // put your dff web bean name here
oaDFF.processFlex(pageContext);


// If you want to set the first Dff (first dff shown in the page) to required then

OAMessageChoiceBean FirstDff = (OAMessageChoiceBean)oaDFF.findIndexedChild("FlexField0");

if (FirstDff != null)
{
FirstDff .setRequired("yes"); // Try to access the value here using getText() or getValue() methods..
}


// If you want to set the  second DFF (second dff shown on page) then

OAMessageChoiceBean SecDffe = (OAMessageChoiceBean)oaDFF.findIndexedChild("FlexField1");

 {
SecDffe .setValue("Default value string"); // Try to access the value here using getText() or getValue() methods..
}

 ========================================================================

OADescriptiveFlexBean oaDFF = (OADescriptiveFlexBean)webBean.findChildRecursive("FlexDFF"); // put your dff web bean name here
oaDFF.processFlex(pageContext);

Enumeration dffElements= null;
dffElements= oaDFF.getChildNames();
while(dffElements.hasMoreElements())
    {
       String DffName = (String)dffElements.nextElement();
       OAWebBean dffbeans=(OAWebBean)oaDFF.findChildRecursive(DffName );
      if(dffbeans != null)
       {                   
           dffbeans.setRequired(OAWebBeanConstants.REQUIRED_NO);
         }

   }
 

Dynamically Changing the VO query in OAF

Generally while creating the View Objects we define a query to it. While rendering the OAF page these view objects get executed (either by framework or by the java code).
In some scenarios we might have a requirement to change the query of the VO, this query can be modified dynamically.

Example: Lets say a VO(EMPVO) has the query as
SELECT empno,ename,sal
FROM EMP

Now I want to change the query as below

SELECT empno,ename,sal+nvl(comm,0)
FROM EMP

Sample code:

String query = “SELECT empno,ename,sal+nvl(comm,0) FROM EMP”;
try
{
OAApplicationModule oam = oapagecontext.getApplicationModule(oawebbean);
// get the handle for your view object
EMPVOImpl voimpl = (EMPVOImpl)oam.findViewObject(“EMPVO”);   
voimpl.setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION);
voimpl.setQuery(query);
// setQuery only sets the new query to the View Object, in order to effect the changes of the query we need to execute the equery using below statement.
voimpl.executeQuery();
}
catch (Exception e)
{
}


Always need to call setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION) before executing the query, If you won’t call this OA Framework will not append the where clause and Order by clauses correctly.
The above code is written in controller, you can write the code in either processRequest method or in processFormRequest based on your requirement or in AM.

API to Create Lookup Values - "FND_LOOKUP_VALUES"

Note: 

Before you start running the Below code, Kindly note we have only inserted records for Language as "US". If one has dual language, write a nested Loop to insert the same.

DECLARE

   CURSOR get_lookup_details

   IS

      SELECT   ltype.application_id,

               ltype.customization_level,

               ltype.creation_date,

               ltype.created_by,

               ltype.last_update_date,

               ltype.last_updated_by,

               ltype.last_update_login,

               tl.lookup_type,

               tl.security_group_id,

               tl.view_application_id,

               tl.description,

               tl.meaning

        FROM   fnd_lookup_types_tl tl, fnd_lookup_types ltype

       WHERE       ltype.lookup_type = 'XX_COUNTRY'

               AND ltype.lookup_type = tl.lookup_type

               AND language = 'US';

   CURSOR get_country

   IS

      SELECT   UPPER (country_name) country FROM xx_country;

   l_rowid   VARCHAR2 (100) := 0;

BEGIN

   FOR i IN get_lookup_details

   LOOP

      FOR j IN get_country

      LOOP

         l_rowid := NULL;

         BEGIN

            fnd_lookup_values_pkg.insert_row (

               x_rowid                 => l_rowid,

               x_lookup_type           => i.lookup_type,

               x_security_group_id     => i.security_group_id,

               x_view_application_id   => i.view_application_id,

               x_lookup_code           => j.country,

               x_tag                   => NULL,

               x_attribute_category    => NULL,

               x_attribute1            => NULL,

               x_attribute2            => NULL,

               x_attribute3            => NULL,

               x_attribute4            => NULL,

               x_enabled_flag          => 'Y',

               x_start_date_active     => TO_DATE ('01-JAN-1950',

                                                   'DD-MON-YYYY'),

               x_end_date_active       => NULL,

               x_territory_code        => NULL,

               x_attribute5            => NULL,

               x_attribute6            => NULL,

               x_attribute7            => NULL,

               x_attribute8            => NULL,

               x_attribute9            => NULL,

               x_attribute10           => NULL,

               x_attribute11           => NULL,

               x_attribute12           => NULL,

               x_attribute13           => NULL,

               x_attribute14           => NULL,

               x_attribute15           => NULL,

               x_meaning               => j.country,

               x_description           => NULL,

               x_creation_date         => SYSDATE,

               x_created_by            => i.created_by,

               x_last_update_date      => i.last_update_date,

               x_last_updated_by       => i.last_updated_by,

               x_last_update_login     => i.last_update_login

            );

            COMMIT;

            DBMS_OUTPUT.put_line (j.country || ' has been loaded');

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);

         END;

      END LOOP;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;