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.
Comments
(22)
Subscribe to this comment's feed
...
written by Anil Passi- , July 09, 2010
written by Anil Passi- , July 09, 2010
Yes, this equally applies to long text, URL documents
A typo - I believe you meant "can also be used"
Votes: +1
A typo - I believe you meant "can also be used"
report abuse
vote down
vote up
document management
written by Panicker , July 11, 2010
written by Panicker , July 11, 2010
Hi Anil,
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?
Appreciate your response.
Regards,
S Panicker
Votes: +0
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?
Appreciate your response.
Regards,
S Panicker
report abuse
vote down
vote up
...
written by Anil Passi- , July 12, 2010
written by Anil Passi- , July 12, 2010
I do not see any issues with loading 200,000 documents against customers. Use the standard functionality for documents.
The only issue for documents in Oracle Apps is that access control is very restrictive.
Firstly 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
Votes: +0
The only issue for documents in Oracle Apps is that access control is very restrictive.
Firstly 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
report abuse
vote down
vote up
...
written by Panicker , July 12, 2010
written by Panicker , July 12, 2010
Thanks Anil, for your quick response.
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?
Votes: -2
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?
report abuse
vote down
vote up
...
written by Anil Passi- , July 12, 2010
written by Anil Passi- , July 12, 2010
Go to responsibility "Application Developer"
Click 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
Votes: +2
Click 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
report abuse
vote down
vote up
...
written by Panicker , July 13, 2010
written by Panicker , July 13, 2010
Fanstatic Anil, that worked. Thanks for your help
Votes: +0
report abuse
vote down
vote up
Attachments - storage and archiving
written by AngelaP , September 06, 2010
written by AngelaP , September 06, 2010
Hello Anil,
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,
Votes: +0
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,
report abuse
vote down
vote up
...
written by Rajesha , September 12, 2010
written by Rajesha , September 12, 2010
Hi Anil,
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
Thanks
Rajesha MS
Votes: +0
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
Thanks
Rajesha MS
report abuse
vote down
vote up
...
written by J@ Document Scanning Service , September 22, 2010
written by J@ Document Scanning Service , September 22, 2010
Hi Anil,
great article looks like a great pioece of EDMS software.
Document Scanning Service
Votes: +1
great article looks like a great pioece of EDMS software.
Document Scanning Service
report abuse
vote down
vote up
DMS and Oracle Interface
written by Munish , September 23, 2010
written by Munish , September 23, 2010
Hi...
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?
Votes: +1
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?
report abuse
vote down
vote up
PO PDF attachment file in PO Outbound XML
written by Eva Evelyn , September 28, 2010
written by Eva Evelyn , September 28, 2010
Hi Anil,
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-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.
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.
Thanks and Regards,
Eva
Votes: +1
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-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.
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.
Thanks and Regards,
Eva
report abuse
vote down
vote up
DM
written by eoracleapps , October 03, 2010
written by eoracleapps , October 03, 2010
Hi Anil
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
eoracleapps.blogspot.com
Votes: +0
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
eoracleapps.blogspot.com
report abuse
vote down
vote up
arey jaffaga
written by sattipandu , October 27, 2010
written by sattipandu , October 27, 2010
no work free salary every month going to office sleeping for 8 hrs?????? life for me is full rest!! no queries on this!!! report abuse
vote down
vote up
...
written by erp , November 27, 2010
written by erp , November 27, 2010
Hi
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
Votes: +0
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
report abuse
vote down
vote up
Integration with UCM
written by varunmittal2005 , February 09, 2011
written by varunmittal2005 , February 09, 2011
Hi Anil,
Can you please let me know some guidelines for Oracle HRMS integration with UCM.
Thanks
Varun
Votes: +0
Can you please let me know some guidelines for Oracle HRMS integration with UCM.
Thanks
Varun
report abuse
vote down
vote up
EMC Documentum image retrieval in Oracle AP Invoice Inquiry form
written by Tushar Patil , June 27, 2011
written by Tushar Patil , June 27, 2011
Hi Anil,
Nice article....
Please 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
Votes: +0
Nice article....
Please 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
report abuse
vote down
vote up
ghd australia
written by ghd australia , August 29, 2011
written by ghd australia , August 29, 2011
If you have not yet tried pink GHD straightener, it's time to own one and feel the difference it can make to your personality. These are just brilliant and fabulous ghd straighteners pink. They are most iconic and can provide you with a new feeling and enhance confidence.
Votes: +0
report abuse
vote down
vote up
Office 2007
written by Office 2007 , December 06, 2011
written by Office 2007 , December 06, 2011
I will keep your new article. I really enjoyed reading this post, thanks for sharing
Votes: +0
report abuse
vote down
vote up
| < Prev | Next > |
|---|
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,
Bharat