Document Management and Oracle EBusiness Suite Attachments - Seven Implementation Options Explained

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

In oracle EBusiness Suite, document/attachments for an employee can exist against various different types of records. To access those documents, the back office staff has to navigate to various different

responsibilities/menu/screens/navigation paths and search for the record to which documents are attached. For example documents can exist against staff's HRMS person record, against their absence records[medical certificates], qualification records [professional certificates], appraisal records, CRM Service requests [complaints, other issues], SIT's, EIT's etc. It is a huge challenge for the back office HRMS staff to access those attached documents in a simple manner from various different places. The navigation to so many screens can be a nightmare for the back office HRMS team, just to pluck out a single document against for one of their staff members.

I have had multiple clients demanding an elegant solution to the issue around access to these EBusiness Suite documents in a secured and simple way.

In this article, I would like to discuss seven different practical implementation options/solutions that can be implemented to achieve a unified yet secured access to documents held within Oracle EBusiness Suite. You can pick the option depending upon your business requirements and subject to the Document Management system that you have implemented.

Before getting into the details, in brief, the seven options are

1. Copy the attachments from different entities into PER_PEOPLE_F entity.

2. Synchronize with Document Management System like Documentum with Oracle EBusiness Suite using third party tools. Also use Attachment repositories to integrate with UCM.

3. Consume document management system's web services, routed via XML Gateway

4. Use SOA Middleware to pull documents from Oracle Apps and push the documents into Document Management System

5. Oracle 11g Database feature to consume document management system's webservice from PL/SQL DB trigger attaching blob attachments

6. Use Business Events, with PL/SQL or Java subscription consuming document management system's webservice

7. Use forms personalization in HRMS Person Screen to enable tools menu

Option 1 - Copy the attachments from different entities into PER_PEOPLE_F entity.

This is a simple solution that can be implemented via PL/SQL code as explained below.

For this a feature/API called copy attachments in Oracle Apps can be used.

For example you can copy the attachments from Absence Entry screen to Person Maintenance screen in HRMS. Likewise, you may also wish to copy the talent appraisal attachments and qualifications related attachments into person screen. The idea is that, all the documents against a person can be made visible in place, i.e. from Person Entry screen. If this is not done, then attachments will remain scattered accross all different screens.

In this option you, these attachments can simply be copied accross from different entities into the person record entity. Before you attempt to understand this approach, you must read the part 1 of the attachment article on this website link http://www.apps2fusion.com/apps-training/apps-functional-documents/ame-sshr/433-attachments-self-service-oracle-apps .

For a document to exist against person record, the entity name must be PER_PEOPLE_F and the primary key must be the person_id. In this scenario, you need to write a piece of PL/SQL or concurrent program to call an API to copy the attachments from one entity & primary key to another target entity & primary key value. This can be done by calling a pl/sql api named edr_attachments_grp.copy_attachments. This api was developed by the EDRM team in Oracle. As you might have guessed, this API takes parameters like from entity name, from primary key, to entity name & to primary key. By using this API, you can shield yourself from the complexity of the multiple table framework that supports the attachments in eBusiness Suite.

The sample code is listed as below

l_incident_id := get_incident_id(p_event_id => v_page_flow_key);

SELECT category_id

INTO l_misc_category_id

FROM fnd_document_categories_vl

WHERE NAME = 'MISC';

FOR p_rec IN (SELECT /*f.media_id,b.document_id,f.datatype_id,*/

b.category_id

/*,b.entity_name*/

FROM fnd_attached_documents b,

fnd_documents f,

fnd_lobs fl

WHERE b.entity_name = 'XXA2F_CS_INCIDENTS_REVIEWERS'

AND b.pk1_value = l_incident_id

AND b.document_id = f.document_id

AND f.datatype_id = 6

AND fl.file_id = f.media_id

GROUP BY b.category_id) LOOP

edr_attachments_grp.copy_attachments(

x_from_entity_name => 'XXA2F_CS_INCIDENTS_REVIEWERS',

x_from_pk1_value => l_incident_id,

x_from_pk2_value => NULL,

x_from_pk3_value => NULL,

x_from_pk4_value => NULL,

x_from_pk5_value => NULL,

x_to_entity_name => 'CS_INCIDENTS',

x_to_pk1_value => l_incident_id,

x_to_pk2_value => NULL,

x_to_pk3_value => NULL,

x_to_pk4_value => NULL,

x_to_pk5_value => NULL,

x_created_by => fnd_global.user_id,

x_last_update_login => fnd_global.login_id,

x_program_application_id => NULL,

x_program_id => NULL,

x_request_id => NULL,

x_automatically_added_flag => 'N',

x_from_category_id => p_rec.category_id,

x_to_category_id => l_misc_category_id);

END LOOP;

Option 2 - Synchronize with Document Management System

Implement document management system and synchronise the documents from different locations into a central document management repository with the employee number reference and other related tags. This approach is useful if your organization already hass an existing document management system which contains documents for almost all paperwork that happens within an Organization. For EMC's Documentum, this is possible via thirdparty softwares. Feel free to get in touch with me if you need details on this. Of course implementing the document management systems has a cost implications. "Option 1" of course is pretty much free.

Option 3 - Consume document management system's web services, routed via XML Gateway

XML Gateway has a great hidden feature which allows you to attach FND_ATTACHED_DOCUMENTS onto the XLM payload. These XML Message payloads will have documents from FND_LOBS attached to the SOAP message itself that can be posted to your trading partner, which in this case is a webservice exposed by your document management system.

To include an attachment in the generated XML message, the XML Gateway must be informed of the relationship between the attachment and the business document. This is accomplished by adding a Procedure Call action to the message map to call the register attachment API at every point in the business document where an attachment is identified. The relationship data is maintained in the ECX_ATTACHMENT_MAPS table, which is used by OTA to construct the outbound XML message with a valid MIME message containing the message payload and associated attachments. The register_attachment API returns a unique content ID (referred to as CID) that is mapped to the OAG ATTCHREF data type, FILENAME element, or other standards-specific equivalent.

Enable Attachments for Unit Test for Outbound Documents

The following outlines the steps necessary to set up attachments for outbound documents:

• Define attachments in the Oracle Foundation module.

• Associate the attachment with the business document.

• Create/update the message map to call the register_attachment API for every off-line attachment associated with the business document.

• Map the off-line attachment (that is, BLOB data type) CID to the ATTCHREF data type, FILENAME element, or other standards-specific equivalent.

• Map the in-line attachment (that is, SHORT_TEXT, LONG_TEXT, or LONG_RAW data type) to the appropriate XML data type or element for text strings.

• Load the map and DTD to the XML Gateway repository.

• Define the Trading Partner, enable the transaction for the Trading Partner, and use protocol type OTAH-ATCH, OTAHS-ATCH, HTTP-ATCH, or HTTPS-ATCH. The OTAH-ATCH and OTAHS-ATCH protocol types are used when an OTA envelope is required. The HTTP-ATCH and HTTPS-ATCH protocol types are used when an OTA envelope is not required.

Option 4 - Use SOA Middleware to pull documents from Oracle Apps and push the documents into Document Management System

If your organization has SOA middleware implemented, specifically SOA 11g, then it is pretty straightforward to leverage that infrastructure. The documents can be read from Oracle via DB adapter and be pushed into document management system by consuming its webservices with SOAP attachments.

Option 5 - Oracle 11g Database feature

Prior to Oracle 11g database, it was not possible to consume webservices by posting BLOBS from database trigger. But in 11g database this becomes possible. You can simply write a tirgger on FND Attachment tables [FND_LOBS] and push the document straight into document management system by calling its webservice, by passing BLOB as SOAP attachment.

Option 6 - Oracle Business Events

Similar to Option 5, however this option is preferred. In this case from DB Trigger you will raise a business event. The subscription of the business event will post the BLOB attachment to the document management system. Please note that unless you are in 11g Database, you will struggle to achieve this via PL/SQL. Reason being that in Version 10g, pl/sql can not process the BLOB greater than 32K in size. This is the root cause of several other attachment related issues in Oracle EBusiness Suite. Regardless, 11g upgrade is a good move to overcome these limitations

Option 7 - A custom OAF form, to which parameters can be passed via custom tools menu in Forms Personalization

In this option, the HRMS backoffice staff can click on custom menu options that zoom to a custom OA Framework page.

This is explained in picture as shown below. This can be achieved quite easily by a combination of Forms Personalization and also a custom OA Framework screen.

Anil Passi

Comments

0 #1 Bharat Daga 2010-07-09 14:08

Anil,

Document of Records functionality can also be sued to attach documents/ url / text against person record? That feature works similar to EITs and suses HR security model. Certain document types can be hidden from responsiblities .

Thanks,
Bhar at

<< May 2024 >>
Mon	Tue	Wed	Thu	Fri	Sat	Sun
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Search Courses

Miscellaneous Technology