Project and Portfolio Management Practitioners Forum
cancel

Obtaining attachments of requests

SOLVED
Go to solution
Highlighted
AlfredoMonasi
Honored Contributor.

Obtaining attachments of requests

Hi everyone,

 

I have a workflow to ejecute an app that builds a pdf according of some data of a request. But now a client is asking me if I could paste a jpg which is an attach in the request. Can anyone give me some suggestions how can I achieve this?, In which tables PPM save the last version of a doccument when its attached to it?. I imagine I have to recover a CLOB field and built it back to its own format.

 

Anyone had a similar approach?

 

Regards,

 

Alfredo

12 REPLIES
Utkarsh_Mishra
Acclaimed Contributor.

Re: Obtaining attachments of requests

Have you configured HP DMS solution or are u still on File system attachments.
Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
AlfredoMonasi
Honored Contributor.

Re: Obtaining attachments of requests

Hi Utkarsh,

 

My test environment is base on File System attachments but the production enviroment of the client will be using the HP DMS solution because the versioning of the attached documents.

Utkarsh_Mishra
Acclaimed Contributor.

Re: Obtaining attachments of requests

Well if you are on DMS then I think... u need to go with custom solution.

 

I have implemented similar solution previously to email PDF as attachment.

 

  1. Create a Java class that saves your extract to JPEG or PDF, store it in any directory.
  2. Use Java smtp api to email the attachments as per your business requirement.
  3. Execute the java class using ppm reports (command like ksc_local_exec java your_file.jar) and schedule it as per your need.

 

You can also use Oracle UTL_SMTP utility to send email with attachment. 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
AlfredoMonasi
Honored Contributor.

Re: Obtaining attachments of requests

Hi Utkarsh,

 

By chance, do you have the Java class to obtain the clob of the image in a request?. I'm using c# as console application to create the pdf and to send it as mail.

 

Regards,

 

Alfredo

Utkarsh_Mishra
Acclaimed Contributor.

Re: Obtaining attachments of requests

I don't have for clob, but I java class for sending html email; you can modify to include attachment.

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
AlfredoMonasi
Honored Contributor.

Re: Obtaining attachments of requests

Hi Utkarsh,

 

So you were sending those images from the file system instead of the database?

 

Regards,

 

Alfredo

Utkarsh_Mishra
Acclaimed Contributor.

Re: Obtaining attachments of requests

Yes, first I generate PDF save it to local system and then send it as attachment.

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
AlfredoMonasi
Honored Contributor.

Re: Obtaining attachments of requests

Thanks Utkarsh,

 

I'll give a look at the data model to see how can I recover the last version of the attachments.

AlfredoMonasi
Honored Contributor.
Solution

Re: Obtaining attachments of requests

Using this query, where P_BIZ_CASE_DOC is the field you need, RD1.PARAMETER3 the value of the document id and 30314 the request_id you can recover the last version of the blob. I'll do a class to write the file to dsk and share it.

 

SELECT DTC.DOCUMENT_ID,DTC.FULL_NAME,D.EXTENSION,DTC.LAST_UPDATE_DATE,DTC.LAST_UPDATE_DATE,DTC.AUTHOR,DTC.DOC_CONTENT
from KNTA_DOCUMENTS D,KNTA_DOCUMENT_TIP_CONTENTS DTC,KNTA_PARAM_SET_FIELDS PSF
WHERE 1=1
AND D.DOCUMENT_ID=DTC.DOCUMENT_ID
AND D.DOCUMENT_HOLDER_ID=PSF.PARAMETER_SET_FIELD_ID
AND D.DOCUMENT_TYPE_ID='FIELD'
AND PSF.PARAMETER_TOKEN='P_BIZ_CASE_DOC'
AND D.DOCUMENT_ID=(SELECT RD1.PARAMETER3 from KCRT_REQ_HEADER_DETAILS rd1 where RD1.BATCH_NUMBER=1 and RD1.REQUEST_ID=30314)

 

Regards,

 

Alfredo

Etienne_Canaud
HPE Expert

Re: Obtaining attachments of requests

Hi,

 

Just keep in mind that this may not work if customer decides to switch from PPM Database DMS to PPM External Database DMS, where only KNTA_DOCUMENTS table is accessible from the PPM Database (all the documents contents are located on the DMS DB).

 

If that's the case, you could eventually hard code the External DMS JNDI address, and get a connection through standard JNDI datasource. You would then get the DOCUMENT_ID from KNTA_DOCUMENTS (PPM DB), and then query the external DMS DB to retrieve the document contents.

If you don't want to hard code the JNDI address, you can retrieve it at runtime from PPM_INT_CONFIGURATIONS DB Table, in the row with SOLUTION_ID = 1200, in the "CONFIGURATION" clob column.

 

That's quite a lot of effort, but since the PPM DMS API is not officially opened for integrations, there's no alternative.

 

Thanks,

Etienne.

AlfredoMonasi
Honored Contributor.

Re: Obtaining attachments of requests

Thanks for the comments Etienne!, Thanksfully the DMS is in the same DB as PPM, but I don't see much work if I have to query it for other DB since I'm doint it for my own class to obtain the attach, thanks for the tip to get the connection String dinamically.

 

Regards,

 

Alfredo

AlfredoMonasi
Honored Contributor.

Re: Obtaining attachments of requests

Using the query above and this method I was able to recover the blob of the document.

 

OracleCommand cmd;
OracleConnection conn;
OracleDataReader reader;

 

public String recuperarBlob(String query){
String resultado=String.Empty;
try
{
int actual = 0;
conn = new OracleConnection(ConfigurationManager.AppSettings["oracleConnectionString"].ToString());
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;

reader = cmd.ExecuteReader();
using (reader)
{
while (reader.Read())
{

//Obteniendo fila incluido el BLOB
String documentID = reader.GetInt32(0).ToString();
String filename = reader.GetString(1);
String extension = reader.GetString(2);
String lastUpdate = reader.GetDateTime(3).ToString();
String author = reader.GetString(4);
OracleLob blob = reader.GetOracleLob(5);

//Construyendo el archivo como un stream
Stream outputStream = File.OpenWrite(ConfigurationManager.AppSettings["carpetaResultadosPE"].ToString() + "\\" + filename);
BufferedStream bufferedOutput = new BufferedStream(outputStream);

//Leyendo el binario en partes.
byte[] buffer = new byte[1024];
while ((actual = blob.Read(buffer, 0, buffer.Length)) > 0)
{
bufferedOutput.Write(buffer, 0, buffer.Length);
bufferedOutput.Flush();
}

bufferedOutput.Close();
}
}

reader.Dispose();
reader.Close();
cmd.Dispose();

}
catch (Exception e)
{
throw new Exception("Error recuperarBlob no pudo leer o escribir de vuelta el archivo: " + e.Message);
}
finally {
conn.Close();
conn.Dispose();
}
return resultado;
}