Version E9.2 - Programmatically attach file based media object

Stank1964

Well Known Member
We plan to use BI Publisher to parse individual invoices into single pdfs and place them on a network share. I wrote a UBE that reads that network share and loops thru the invoice pdf files and sends emails out to customers.

I need that UBE to attach the pdf to the AR records in F03B11. Has anyone done anything like this? I could use some guidance on how to go about this. I think I may need some C code but that is outside of my skill set....

Thanks.

Stan
 
You can create the F00165 records that point to the files in a MO Queue. Check out existing records that are created when you manually do an attachment. The important bit is knowing the key to the document you need to link to. If your filename has that (Company,Doc Typ,Order#), you can parse that out and create the piped string for the GDTXKY column of the GT03B11 attachment.

Craig
 
Thanks for the reply Craig.

The problem is that at 9.2 we are set to store the attachments in the database. When you do that, 2 new files get into the mix, F98MOQUE and F98MODAT, the file name value in F00165 (GDGTFILENM) seems to just be a pointer to the F98MODAT record as a file at that location does not exist. My understanding is that the actual attachment gets encapsulated into a blob field in F98MODAT - field is ZUTXFT.

Oracle tells me I need a C BSFN that would serve as a "wrapper" to an API that I can call from a UBE. Does this make sense?

If so, unfortunately, I can't write C code.

Thanks. Stan
 
Stan,

There is an option to store attachments on the file system instead of the database. We also do a type of file attachment in an automated way, and this was one of the driving decisions for us to keep our MO's on a file system instead of the database. Plus, database storage for us is more expensive than on a file system. But this is a business decision that each JDE customer has to make. My 2 cents.
 
Hey Don. Thanks for the reply.

I hear you about the option, I am not sure why our CNC made the change as I was never party to a conversation about it. I did see (on the Oracle) something about where it was not option prior to some level of E920, so perhaps we are or were at that level at some point. In any event, we're storing attachments in the database.

Can someone advise me about the effort to create a "wrapper" C BSFN that would be callable from a UBE?

Thanks again. Stan
 
i've written some C to put date into F98MODAT (where the attachment blob is stored)

sry for the formatting - this does not support large attachments that would use multiple F98MODAT records

we've got a custom set of tables and bsfn's for attaching stuff from BI to records (mo attachments). Pretty slick.

read the file into memory
Code:
   pFile = jdeFopen(szFullFilePath, _J("rb"));   if (pFile==NULL){	      /*debug*/      jdeWriteLog(_J("B559800"), 0, _J("ERROR OPENING FILE"));      jdeWriteLog(_J("B559800"), 0, szFullFilePath);      jdeStrncpy(dsDE0022.szDescription, (const JCHAR *)(jdeRemoveLeadingAndTrailingSpaces(szFullFilePath)),         DIM(dsDE0022.szDescription));      jdeSetGBRErrorSubText(lpBhvrCom, lpVoid, 0, _J("Y55FOPEN"), &dsDE0022);	   idReturnCode = ER_ERROR;	   goto FunctionCleanup;	   }else{	   jdeFseek(pFile, 0, SEEK_END);                           // Jump to the end of the file	   lFilelen = jdeFtell(pFile);                             // Get the current byte offset in the file	   jdeFseek(pFile, 0, SEEK_SET);                           // Jump back to the beginning of the file	   pBuffer = (uchar *)jdeReAllocEx(pBuffer,lFilelen +1);   // Enough memory for file 	   jdeFread(pBuffer, 1, lFilelen, pFile);                  // Read in the entire file	      }

/*the insert bit*/

   jdeStrcpy(dsDbRef.szTable, NID_F98MODAT);   dsDbRef.idInstance = 0;   jdeStrcpy(dsDbRef.szDict, NID_TXFT);   dsF98MODAT.zutxft.lMaxSize = MAX_BLOB_SIZE;   dsF98MODAT.zutxft.lSize = lFilelen;   dsF98MODAT.zutxft.lpValue = JDB_SetBLOBSize(hF98MODAT, dsDbRef, dsF98MODAT.zutxft.lSize);   memcpy(dsF98MODAT.zutxft.lpValue, (void *)pBuffer, dsF98MODAT.zutxft.lSize);      if (JDB_InsertTable(hF98MODAT, NID_F98MODAT, 0, &dsF98MODAT) != JDEDB_PASSED){      jdeStrncpy(dsDE0022.szDescription, (const JCHAR *)(_J("F98MODAT")),         DIM(dsDE0022.szDescription));      jdeSetGBRErrorSubText(lpBhvrCom, lpVoid, 0, _J("078F"), &dsDE0022);	   idReturnCode = ER_ERROR;	   goto FunctionCleanup;   }
 
Last edited:
Just my opinion, but if you are going to use C code you might as well use the plethora of JDE media object C APIs for this. That way if Oracle changes the underlying implementation of how media objects are stored or your organizations makes a configuration change as to where they are stored (file system, db, etc.) your code should stay the same.
 
Just my opinion, but if you are going to use C code you might as well use the plethora of JDE media object C APIs for this. That way if Oracle changes the underlying implementation of how media objects are stored or your organizations makes a configuration change as to where they are stored (file system, db, etc.) your code should stay the same.

at the time i rolled my own - there was no api for the in database storage.(we are on 9.2.1.4) :(

E1: MOBJ: New Media Object APIs to Work with Attachments Stored in F98MODAT Table from Tools Release 9.2.1.6 (Doc ID 2301138.1)
 
at the time i rolled my own - there was no api for the in database storage.(we are on 9.2.1.4) :(

E1: MOBJ: New Media Object APIs to Work with Attachments Stored in F98MODAT Table from Tools Release 9.2.1.6 (Doc ID 2301138.1)

Sorry, I meant this as more of a general comment, wasn't specifically criticizing you or what you did in the past. We all have to do things like this from time to time and better ways of doing things come along later.
 
Sorry, I meant this as more of a general comment, wasn't specifically criticizing you or what you did in the past. We all have to do things like this from time to time and better ways of doing things come along later.

100% agree. I didn't take offense. I looked for the JDE api's for this stuff and it wasn't realeased. took no pleasure in learning about blobs and F98MODAT
 
So, Oracle provides an API that I think is the right one for this job - JDB_InsertMediaObjectFile. (Document 2301138.1)

I can't call that API from an E1 app or UBE. Can someone give me an idea of what is reasonable in terms of effort (hours) to create a C BFSN wrapper for this API?

Can someone give me an example of what this would look like?

Thanks for all the help!

Stan
 
oof. depends on your knowledge of C and jde BSFNs :)

It could range from weeks to months
 
Most of my work with media objects is on TR 9.1 so I have never used those specific APIs but they look pretty simple based on the documentation.

"Effort" depends on a lot more than just those specific APIs however. If everything is setup and you are talking about creating a BSFN that simply takes a file UNC path and then calls that API. That little piece of the solution is trivial... if it works as advertised.
 
Hello JDEList,

Bumping this one back up.

Have any of you got JDB_InsertMediaObjectFile to work?

I have this DS (I suffixed them either "in" or "out" based on the spec in Oracle Support Doc ID 2301138.1):

typedef struct tagDSD550003C
{
JCHAR szQueueName_in[31];
JCHAR szFileName_in[255];
JCHAR szQueueName_out[31];
JCHAR szFileName_out[255];
} DSD550003C , *LPDSD550003C;


And have this API call in the BSFN:

eJDBReturn = JDB_InsertMediaObjectFile(hUser, lpDS->szQueueName_in, lpDS->szFileName_in, lpDS->szQueueName_out, DIM(lpDS->szQueueName_out), lpDS->szFileName_out, DIM(lpDS->szFileName_out));

Here is that we see in the debug log:

**********************************************************************************
*** Start dumping data structure for business function Insert_F98MODAT_Record
**********************************************************************************
IN->[ 1] <Item>: szQueueName_in <type>: STRING <Value>: [TestImages]
IN->[ 2] <Item>: szFileName_in <type>: STRING <Value>: [\\JDEData\data\Images\E0859BFC53DB40AC8DC7_1D0370817A473F9990F33DB241E0B6E3_170.pdf]
IN->[ 3] <Item>: szQueueName_out <type>: STRING <Value>: [TestImages]
IN->[ 4] <Item>: szFileName_out <type>: STRING <Value>: [E0859BFC53DB40AC8DC7_1D0370817A473F9990F33DB241E0B6E3_170.pdf]
**********************************************************************************
*** End dumping data structure for business function Insert_F98MODAT_Record
**********************************************************************************
Entering jdeCloseDictionary
Exited jdeCloseDictionary with DDType 0
JDB9901272 - JDB_InsertMediaObjectFile - NULL arguments not allowed, except szTargetQueueName
**********************************************************************************
*** Start dumping data structure for business function Insert_F98MODAT_Record
**********************************************************************************
OUT->[ 1] <Item>: szQueueName_in <type>: STRING <Value>: [TestImages]
OUT->[ 2] <Item>: szFileName_in <type>: STRING <Value>: [\\JDEData\data\Images\E0859BFC53DB40AC8DC7_1D0370817A473F9990F33DB241E0B6E3_170.pdf]
OUT->[ 3] <Item>: szQueueName_out <type>: STRING <Value>: []
OUT->[ 4] <Item>: szFileName_out <type>: STRING <Value>: []
**********************************************************************************
*** End dumping data structure for business function Insert_F98MODAT_Record
**********************************************************************************

None of the parameters are NULL. Parameters 3 & 4 become null after calling the API. Also it's strange to me that the error is saying TargetQueueName can be null because according to Oracle Support Doc ID 2301138.1 that is an input parameter and I assume would be required.

Any ideas what I'm doing wrong?

Thanks,
Wade
 
Hi Wade,

I spent lot of time to have JDB_InsertMediaObjectFile working in order to automatically upload my PDF and XLS files (electronic invoice) as media objects into F98MODAT.

Finally I did succeed (btw my MO is named GT55EI04) but I don't know if my C piece of software is really the most correct / elegant.
Maybe it's something linked to "Miscellaneous" queue name I'm using (please look at your P98MOQUE setup).

Here you are:

C++:
/**************************************************************************
 *  Business Function:  G55UploadMO
 *
 *        Description:  G55 Upload MO
 *
 *         Parameters:
 *           LPBHVRCOM           lpBhvrCom    Business Function Communications
 *           LPVOID              lpVoid       Void Parameter - DO NOT USE!
 *           LPDSD55E            lpDS         Parameter Data Structure Pointer   
 *
 *************************************************************************/
 
JDEBFRTN (ID) JDEBFWINAPI G55UploadMO (LPBHVRCOM lpBhvrCom, LPVOID lpVoid, LPDSD55EI04D lpDS) 
 
{
   /************************************************************************
    *  Variable declarations
    ************************************************************************/
    HUSER                hUser = (HUSER)NULL;
    ID                    idJDBResult = JDEDB_PASSED;
    ID                    idReturn = ER_SUCCESS;
    JDEDB_RESULT        JDBReturn = JDEDB_PASSED;
    HREQUEST            hRequestGT = NULL;
    JCHAR                szObjectName[11] = _J("GT55EI04");
    DSGT55EI04            dsGTKey = { 0 } ;
    JCHAR                szLang[3] = _J("");
    MODATA                dsGTNewData = { 0 };
    JCHAR                szTargetQueueName[31];
    JCHAR                szSourceFileName[255]; // name of input file from disk
    JCHAR                szQueueName[31] = _J(""); // queue name column in F98modat
    JCHAR                szFileName[255] = _J(""); // file name column in F98modat


   /************************************************************************
    * Declare structures
    ************************************************************************/
 
   /************************************************************************
    * Declare pointers
    ************************************************************************/
 
   /************************************************************************
    * Check for NULL pointers
    ************************************************************************/
   if ((lpBhvrCom == (LPBHVRCOM) NULL) ||
       (lpVoid    == (LPVOID)    NULL) ||
       (lpDS      == (LPDSD55EI04D)    NULL))
   {
     jdeErrorSet (lpBhvrCom, lpVoid, (ID) 0, _J("4363"), (LPVOID) NULL);
     return ER_ERROR;
   }

   idJDBResult = JDB_InitBhvr((void *)lpBhvrCom, &hUser,
       (JCHAR *)NULL, JDEDB_COMMIT_AUTO);

   if (idJDBResult == JDEDB_FAILED)
   {
       jdeErrorSet(lpBhvrCom, lpVoid, (ID)0, _J("078S"), (LPVOID)NULL);
       return ER_ERROR;
   }
 
   /************************************************************************
    * Set pointers
    ************************************************************************/
 
   /************************************************************************
    * Main Processing
    ************************************************************************/
   MathCopy(&dsGTKey.mnSDIno_DOCD, &lpDS->mnSDIno_DOCD);
   /* Open F00165 Table */

   JDBReturn = jdeGT_OpenTable(hUser, NULL, szObjectName, &hRequestGT);

   if (JDBReturn == JDEDB_PASSED)
   {
       jdeStrncpyTerminate((JCHAR *)(szTargetQueueName), (const JCHAR *)(_J("Miscellaneous")),
               DIM(szTargetQueueName)); 

       jdeStrncpyTerminate((JCHAR *)(szSourceFileName), lpDS->szFile_K74PATH,
           DIM(szSourceFileName));

       JDBReturn = JDB_InsertMediaObjectFile(hUser, szTargetQueueName, szSourceFileName,
           szQueueName, DIM(szQueueName), szFileName, DIM(szFileName));
       if (JDBReturn != JDEDB_PASSED)
       {
           jdeTraceSz(NULL, _J("JDB_InsertMediaObjectFile failed."));
           /* After insert fails, nothing to select or delete. */
           idReturn = ER_ERROR;
       }
       else
       {
           dsGTNewData.nSeq = lpDS->nMOseqNo_MOSEQN;
           dsGTNewData.nMOType = OBJ_JDEIMAGE; /* Which is ok for PDF - MOqueue Mischellaneous*/
           dsGTNewData.bRTFData = FALSE;
           dsGTNewData.pData = NULL;
           jdeStrcpy((JCHAR *)(dsGTNewData.szFileName), (const JCHAR *)(szFileName));
           jdeStrcpy((JCHAR *)(dsGTNewData.szQueueName), (const JCHAR *)(szQueueName));

           jdeStrcpy(dsGTNewData.szItemName, (const JCHAR *)(szFileName));
           JDBReturn = jdeGT_InsertData(hRequestGT, szObjectName, &dsGTKey, 1, szLang, &dsGTNewData);
           if (JDBReturn == JDEDB_FAILED)
           {
               idReturn = ER_ERROR;
           }
       }
   }
   else
   {
       idReturn = ER_ERROR;
   }


   /************************************************************************
    * Function Clean Up
    ************************************************************************/
   jdeGT_CloseTable(hRequestGT);

   return (idReturn);

Kind regards,

Carlo
 
Hey Carlo,

Thanks for your input. I was able to get the function to work. The null parameter error I was getting was because I wasn't initializing hUser using JDB_InitBhvr. oops.

Here is the code BSFN full code:


JDEBFRTN (ID) JDEBFWINAPI Insert_F98MODAT_Record (LPBHVRCOM lpBhvrCom, LPVOID lpVoid, LPDSD550003C lpDS)

{
/************************************************************************
* Variable declarations
************************************************************************/

ID idReturnCode = ER_SUCCESS;
HUSER hUser = (HUSER) NULL;
JDEDB_RESULT eJDBReturn = JDEDB_PASSED;

/************************************************************************
* Declare structures
************************************************************************/

/************************************************************************
* Declare pointers
************************************************************************/

/************************************************************************
* Check for NULL pointers
************************************************************************/
if ((lpBhvrCom == (LPBHVRCOM) NULL) ||
(lpVoid == (LPVOID) NULL) ||
(lpDS == (LPDSD550003C) NULL))
{
jdeErrorSet (lpBhvrCom, lpVoid, (ID) 0, _J("4363"), (LPVOID) NULL);
return ER_ERROR;
}

/************************************************************************
* Set pointers
************************************************************************/

/************************************************************************
* Main Processing
************************************************************************/
eJDBReturn = JDB_InitBhvr (lpBhvrCom, &hUser, (JCHAR *) NULL,
JDEDB_COMMIT_AUTO);

eJDBReturn = JDB_InsertMediaObjectFile(hUser, lpDS->szQueueName_in, lpDS->szFileName_in, lpDS->szQueueName_out, DIM(lpDS->szQueueName_out), lpDS->szFileName_out, DIM(lpDS->szFileName_out));
if(eJDBReturn != JDEDB_PASSED)
idReturnCode = ER_ERROR;


/************************************************************************
* Function Clean Up
************************************************************************/

if (hUser != NULL)
{
JDB_FreeBhvr(hUser);
}

return (idReturnCode);
}
 
Hi..
I developed BSFN to insert data into F98MODAT. It is working fine locally and i received the output file path from API.
But when I run it in the server I am getting error : JDB9901263 - JDB_InsertMediaObjectFile failed to open source file \\Trjdemgr\e920\mediaobj\htmlupload\Test\18345_00001_201422_DebitMemo.pdf

F98MOQUE is with proper queue name. I am using the above BSFN in End Report(future),because I am using BI output as input to BSFN. Kindly help on this error.
**********************************************************

/************************************************************************
* Variable declarations
************************************************************************/

HUSER hUser = (HUSER)NULL;
ID idJDBResult = JDEDB_PASSED;
ID idReturn = ER_SUCCESS;
JDEDB_RESULT JDBReturn = JDEDB_PASSED;

JCHAR szTargetQueueName[31];
JCHAR szSourceFileName[255]; /* name of input file from disk*/
JCHAR szQueueName[31] = _J(""); /* queue name column in F98modat*/
JCHAR szFileName[255] = _J(""); /* file name column in F98modat*/





/************************************************************************
* Declare structures
************************************************************************/

/************************************************************************
* Declare pointers
************************************************************************/

/************************************************************************
* Check for NULL pointers
************************************************************************/
if ((lpBhvrCom == (LPBHVRCOM)NULL) ||
(lpVoid == (LPVOID)NULL) ||
(lpDS == (LPDSD5598M01)NULL))
{
jdeErrorSet(lpBhvrCom, lpVoid, (ID)0, _J("4363"), (LPVOID)NULL);
return ER_ERROR;
}

idJDBResult = JDB_InitBhvr((void *)lpBhvrCom, &hUser,
(JCHAR *)NULL, JDEDB_COMMIT_AUTO);

if (idJDBResult == JDEDB_FAILED)
{
jdeErrorSet(lpBhvrCom, lpVoid, (ID)0, _J("078S"), (LPVOID)NULL);
return ER_ERROR;
}


/************************************************************************
* Set pointers
************************************************************************/

/************************************************************************
* Main Processing
************************************************************************/




jdeStrncpyTerminate((JCHAR *)(szTargetQueueName), (const JCHAR *)(_J("HTMLUpload")),
DIM(szTargetQueueName));

jdeStrncpyTerminate((JCHAR *)(szSourceFileName), lpDS->szGTFileName,
DIM(szSourceFileName));

JDBReturn = JDB_InsertMediaObjectFile(hUser, szTargetQueueName, szSourceFileName,
szQueueName, DIM(szQueueName), szFileName, DIM(szFileName));


if (JDBReturn != JDEDB_PASSED)
{
jdeTraceSz(NULL, _J("JDB_InsertMediaObjectFile failed."));
/* After insert fails, nothing to select or delete. */
idReturn = ER_ERROR;
}
else
{
jdeStrncpyTerminate((JCHAR *)(lpDS->szGTFileNameReturn), szFileName,
DIM(lpDS->szGTFileNameReturn));



}


/************************************************************************
* Function Clean Up
************************************************************************/


if (hUser != NULL)
{
JDB_FreeBhvr(hUser);
}

return (idReturn);

}

***************************************************************************************
 
Hi..
I developed BSFN to insert data into F98MODAT. It is working fine locally and i received the output file path from API.
But when I run it in the server I am getting error : JDB9901263 - JDB_InsertMediaObjectFile failed to open source file \\Trjdemgr\e920\mediaobj\htmlupload\Test\18345_00001_201422_DebitMemo.pdf

F98MOQUE is with proper queue name. I am using the above BSFN in End Report(future),because I am using BI output as input to BSFN. Kindly help on this error.
**********************************************************

/************************************************************************
* Variable declarations
************************************************************************/

HUSER hUser = (HUSER)NULL;
ID idJDBResult = JDEDB_PASSED;
ID idReturn = ER_SUCCESS;
JDEDB_RESULT JDBReturn = JDEDB_PASSED;

JCHAR szTargetQueueName[31];
JCHAR szSourceFileName[255]; /* name of input file from disk*/
JCHAR szQueueName[31] = _J(""); /* queue name column in F98modat*/
JCHAR szFileName[255] = _J(""); /* file name column in F98modat*/





/************************************************************************
* Declare structures
************************************************************************/

/************************************************************************
* Declare pointers
************************************************************************/

/************************************************************************
* Check for NULL pointers
************************************************************************/
if ((lpBhvrCom == (LPBHVRCOM)NULL) ||
(lpVoid == (LPVOID)NULL) ||
(lpDS == (LPDSD5598M01)NULL))
{
jdeErrorSet(lpBhvrCom, lpVoid, (ID)0, _J("4363"), (LPVOID)NULL);
return ER_ERROR;
}

idJDBResult = JDB_InitBhvr((void *)lpBhvrCom, &hUser,
(JCHAR *)NULL, JDEDB_COMMIT_AUTO);

if (idJDBResult == JDEDB_FAILED)
{
jdeErrorSet(lpBhvrCom, lpVoid, (ID)0, _J("078S"), (LPVOID)NULL);
return ER_ERROR;
}


/************************************************************************
* Set pointers
************************************************************************/

/************************************************************************
* Main Processing
************************************************************************/




jdeStrncpyTerminate((JCHAR *)(szTargetQueueName), (const JCHAR *)(_J("HTMLUpload")),
DIM(szTargetQueueName));

jdeStrncpyTerminate((JCHAR *)(szSourceFileName), lpDS->szGTFileName,
DIM(szSourceFileName));

JDBReturn = JDB_InsertMediaObjectFile(hUser, szTargetQueueName, szSourceFileName,
szQueueName, DIM(szQueueName), szFileName, DIM(szFileName));


if (JDBReturn != JDEDB_PASSED)
{
jdeTraceSz(NULL, _J("JDB_InsertMediaObjectFile failed."));
/* After insert fails, nothing to select or delete. */
idReturn = ER_ERROR;
}
else
{
jdeStrncpyTerminate((JCHAR *)(lpDS->szGTFileNameReturn), szFileName,
DIM(lpDS->szGTFileNameReturn));



}


/************************************************************************
* Function Clean Up
************************************************************************/


if (hUser != NULL)
{
JDB_FreeBhvr(hUser);
}

return (idReturn);

}

***************************************************************************************
The issue was resolved with the installation of the latest package.
Thank you
 
Back
Top