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
![](/old/images/stories/anilblog.png)
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
![](/old/images/stories/contributors/anilpassi/copy-attachments/copy-attach-01.png)
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.
![](/old/images/stories/contributors/anilpassi/copy-attachments/copy-attach-02.png)
Comments
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
A typo - I believe you meant "can also be used" :)
Very nice article.
I have a query.
We use Oracle Financials 11i.Our credit manager has come up with request to have a provision in the application whereby he can upload credit letters and related documents against customers in AR. I have looked through documentation and found that 'Case Folder' in Oracle Credit Management has a facility to upload documents. However the Workflow of the Case folder is much beyond the scope of our business. Also users have to navigate to so many screens to reach the attachment option in case folder so our guys are not very keen to use this functionality.
Created an SR with Oracle, but they are of the opinion that there isn't any option to upload documents in the system. SAP has this functionality and so many organizations are using this effectively here in Australia.
We have around 100,000 old documents that should go against customers and a daily growth of 100 files. Is there a way we can upload documents in the database through application? Do you anticipate any performance related issues once these files are uploaded?
Appr eciate your response.
Rega rds,
S Panicker
The only issue for documents in Oracle Apps is that access control is very restrictive.
F irstly check if document attachment icon is visible on that screen
If not, then my my suggesiton is that
1. Create a simple custom OAF Page that allows documents upload/views
2. Pass customer id or any other id via personalization to the custom region
3. You can apply additional custom logic for securing document access based on some profile etc
If you are in R12, then customer page is OAF, which means the document region can be embedded within the standard page by extension.
Thanks,
Anil Passi
The attachment icon is visible on top of the screen , but its not enabled. Do you think you can suggest something to enable this button?
Clic k on menu labeled Attachments and then click Attachment Functions
Here you can define or query which Blocks within the Oracle Forms will display attachment icon for a function
Please use the three screens within Attachment Menu of Application Developer to enable the attachment icon
Thanks for the fantastic article. I've been doing research on the attachments functionality, specifically for iExpenses, which we would like to start using to eliminate hard copy receipts. I have not been able to find any information on implications on storage, performance and archiving if attachments are heavily used throughout the apps. We are using 11.5.10. Is there an archiving process for attachments should we want to purge these after a number of years?
thanks,
Sorry to inturrutp in thid blog.. We are using oracle applications 11.5.10.2 and planning to get ready for extended support which is effective from 01-Dec-2010. Do you any reference document in your blog.. (other than metalink document) sharing your experience
Tha nks
Rajesha MS
great article looks like a great pioece of EDMS software.
Docum ent Scanning Service
Thanks for this article.
We are looking for an interface of Oracle attachments and EDMS without uploading documents in FND_LOB table.
EX. We will click on attachment link on people form which will open a custom OAF page where we will select the document and click on apply. Document (attachment) should be saved in DMS which in return will provide a key and we will save this key with other details in a custom table in Oracle to refer EDMS.
Is there a way to achieve above requirement thrrough Web Services? or PLSQL?
Can you please email me any sample code for the same?
This is a great article and we need do exactly the same as you have mentioned in the "Option 3 - Consume document management system's web services, routed via XML Gateway".
I just need some detailed steps for the two points you had mentioned.
- Map the off-line attachment (that is, BLOB data type) CID to the ATTCHREF data type, FILENAME element, or other standards-speci fic 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.
Our specific requirement is to attach the PO PDF in the PO Outbound XML. We need to customize "itg_process_po _007_out.xgm" for the same. The DTD for this XGM already has a syntax for ATTCHREF, but only the Filename is added to the XGM.
Can you tell which field above should be mapped to the CID data type?
Also, what is the difference between the off-line attachment and in-line attachment. Incase of PO PDF should we use the off-line attachment method or in-line attachment method?
It would be great help if you can provide some code example for implementing the option3.
Thank s and Regards,
Eva
Very Interesting article. Just want to say that I have refer your book on R12 and Extensions for OAF and that is mind blowing , already recommenced to couple of my friends. Thanks
eoracl eapps.blogspot. com
I am familier with financial moduls and concepts in Erp 11i but,i didnt set up it. Since ,at the present, I have to implement release 12 ,I want to know which courses are more suiteble for me.
Thank you in advance and look forward to hear from you as soon as possible
Can you please let me know some guidelines for Oracle HRMS integration with UCM.
Thanks
Va run
UBB==>canada goose jackets
Nice article....
Pl ease provide me if you have any documents available to retrieve the EMC Documentum Images in Invoice Inquiry form using custom built-in. If you know any specific web services API's to be call and the event name to be used to trigger this, then it will be great.
Thanks & Regards,
Tushar Patil
Thanks for this post.
We have requirement with one or prestigious client to integrate Oracle EBS R12.2.3 with EMC Documentum. Coupld you please guide me how I can achieve this?
Thanks in advance.
Regards,
Manoj Madhusoodanan Nair
submit is simply nice and that i can think
you're an expert in this subject. Fine with your permission allow me to
grasp your feed to stay up to date with coming near
near post. Thanks one million and please continue the enjoyable work.
I'm planning to start my own website soon but I'm a little lost on everything.
Would you propose starting with a free platform like Wordpress or go for a paid option? There are so many choices out there that I'm totally overwhelmed ..
Any ideas? Thanks a lot!
It's a very easy on the eyes which makes it much more enjoyable for
me to come here and visit more often. Did you hire out a designer to
create your theme? Fantastic work!
to be shared around the net. Shame on Google for
now not positioning this submit higher! Come on over and seek advice from my site .
Thanks =)
it through yahoo.
I'm having some minor security issues with my latest
website and I'd like to find something more risk-free.
Do you have any suggestions?
got here up, it looks good. I've bookmarked it in my google bookmarks.[X-N- E-W-L-I-N-S-P-I -N-X]Hello there,
just changed into alert to your weblog via Google, and located that it's really informative.
I am going to be careful for brussels. I will be grateful in the event you continue this in future.
A lot of people can be benefited from your writing.
Cheers!
Do you have any tips on how to get listed in Yahoo
News? I've been trying for a while but I never seem to get
there! Thanks
Look complicated to far introduced agreeable from you!
By the way, how can we keep in touch?
points you've made.
know-how here with mates.
A number of my blog readers have complained about my blog not working correctly in Explorer but looks great
in Opera. Do you have any suggestions to help fix
this problem?
Also visit my webpage ... cannabis
delivery: http://cannabisdispensarydeliver75173.blogrelation.com/14382918/figuring-out-woman-and-male-cannabis-crops
RSS feed for comments to this post