Mass download of contract documents from agreement to FTP/SFTP
In sourcing we don’t have any standard option for mass download of all the contract documents at a time from all the agreements. Only option we have that download the documents one by one manually. If we have 10 -15 documents, we can do it by manually but when we have huge number of documents to download it is very painful to download one by one and time consuming process also.
To overcome from above manual activity, we have built a custom explicitly called script and downloaded all the documents to FTP directly.
This blog intended to give the concept and customization details require for this.
- Maintain File Transfer Configuration in Sourcing.
- FTP configuration should test in sourcing prior to run the script.
- FTP path should have required space to download the documents.
Development Overview/Technical Details
Step 1: Develop a custom query definition to get the agreements list with contract document name and unique document id. The query definition will look like below one. You can implement the query based on your requirement condition.
SELECT DISTINCT <%RESULTS%> FROM
( SELECT T1.UNIQUE_DOC_NAME AS CUNIQUE_DOC_NAME , T1.OBJECTID AS CONTRACTID , 1004 AS CLASSID
FROM <%SCHEMA%>.FCI_CONTRACT T1 WHERE T1.INACTIVE = 0 AND T1.IS_TEMPLATE =0
AND T1.CONTEXTID =<%CONTEXT(contracts.Contract)%>
LEFT OUTER JOIN
B.PARENT_OBJECT_ID AS PARENT_OBJECT_ID,
B.PARENT_CLASS_ID AS PARENT_CLASS_ID ,
C.DOC_DISPLAY_NAME AS LATEST_VERSION ,
C.UNIQUE_DOC_NAME AS DOCUNI
C.PARENT_OBJECT_ID = B.OBJECTID
CONTRACT.CONTRACTID = DOCS.PARENT_OBJECT_ID
AND DOCS.PARENT_CLASS_ID = CONTRACT.CLASSID
Query result fields
Query result data
Step 2: Then develop a custom explicitly called script.
i) In this script using Query IAPIs we executed the above developed query definition to get the result set.
HashMap contractdocuments = new HashMap(); queryExecution = IapiQueryExecFactory.createQueryExec( session,”Custom-Exportalldocuments” );
parameterSet = queryExecution.getParameterSet( session, “Custom-Exportalldocuments” );
resultSet = queryExecution.execute( contractdocuments );
metaData = resultSet.getMetaData();
ii) Once the result set is available, Iterate the result set to get the Agreement id, document file name and document unique id.
while ( resultSet.next() )
agreementId = resultSet.getString( 0 );
documentFile = resultSet.getString( 1 );
documentUniquename = resultSet.getString( 2 );
iii) Find the document version by document unique name.
IBeanHomeIfc documenthome = IBeanHomeLocator.lookup( session,DocumentVersionIBeanHomeIfc.sHOME_NAME );
DocumentVersionIBeanIfc docVersion = ( ( DocumentVersionIBeanHomeIfc ) documenthome ).findByUniqueDocName( documentUniquename ); //Find the contract document by uniquename
DocumentVersionIBeanHomeIfc documentbeanhome = ( DocumentVersionIBeanHomeIfc ) docVersion.getIBeanHomeIfc();
AttachmentIfc attachment = docVersion.getDoc(); //Get the document
iv) Using version get the document file.
File fileData = attachment.getFileData( session ); //Get the document file
v) Fetch the FTP/SFTP path by passing the FTP configuration external id.
ftcHome = HomeLocator.lookup( session, “odp.doccommon.FileTransferConfig” );
FileTransferConfigBo ftppath = ftcHome.findByExternalId( “101 Unit Categories Text” );
FileTransferClientIfc ftp = ftppath.getClient();
vi) Once FTP path available place the file with require name.
ftp.putFile( datafile, ftppath.getUrl() +agreementId+ ” – ” +attachment.getDisplayName() ); //Place the file in FTP path with required name, we saved the file in FTP with agreement id as prefix to identify easily.
ftp.close(); //always make sure you close the ftp client
Step 3: Create a schedule task to run the explicitly called script.
Step 4: Once task completed, check the files in FTP path.
- You can test above script initially as normal script with Scripting Context as Document Life cycle Event, Class as Master Agreement (1004) and Target as Saved. You can pass only one contract and test it.
If there are any errors, you will be able to see while saving the agreement and resolve easily.
- I gave example for Master Agreement in this presentation, same can be applicable to Sub Agreements as well.
- In my example I used FTP, we have an option to use SFTP also. SFTP details need to be configured in FTC.
- You can use Try – catch block to execute above script and capture the error.
- ZIP file download is not supported by this approach.
- Above approach can be easily implement.
- We can reduce manual effort and time by this approach.
- Access agreement id and contract document easily based on requirement via query definition.
- System performance will not affect much but little bit slowness.
- Maintenance of this code and transport to higher environments is also easy.