SQL statements in the script for CLM

[samta_nichani3]

Hello All,

We have a nightly script running on CLM server and it was working fine with CLM 5.1.

However the recent updates have issues and it doesnt seem to working correctly with CLM 7.0

We raised the issue to SAP as well and SAP recommended that we have direct SELECT and DELETE SQL statements in the code which is not supported. Can anyone please help whats the alternative method of re-writing these and how can we find a published API for these functionalities.

Regards,
Samta.

[vikram_shanmugasundaram]

Hi Samta,

The recommended approach is to use the published APIs. You can find all published APIs in the Reference Guide. If you could provide us with some specifics on what you are trying to accomplish, the forum might be able to provide some suggestions.

Regards,

Vikram

[Former Member]

Hi Vikram,

Thanks in advance for your sugeestion and help.

I am working on one requirement, that is Uploading RFX data into CLM system based on the condition check on DOCUMENT_DESCRIPTION of FCI_CONTRACT table for Master Agreement exsistence in CLM system as UNIQUE_DOC_NAME is always empty in inbound RFX data.

If any Master Agreement's DOCUMENT_DESCRIPTION is matched with the value of DOCUMENT_DESCRIPTION which is coming from RFX inbound data then we have to update the existing Master Agreement for the below columns.

  DISPLAY_NAME
  EFFECTIVE_DATE
  RENEWAL_REMINDER
  EXPIRATION_DATE
  RETENTION_DATE
  DATE_TO_PUBLISH
  BUYER_SEARCHABLE
  VENDOR_VISIBLE
  CALC_METHOD
  COLLABORATOR_1
  COLLABORATOR_2
  COLLABORATOR_3
  GROUP_1
  GROUP_2
  GROUP_3
  UNIQUE_DOC_NAME
  DOC_TYPE
  STATUS
  INT_CAT
  EXT_CAT
  CURRENCY
  VENDOR
  CONTACT
  COMPANY
  BUSINESS_UNIT
  LOCATION
  DOC_OWNER_USER
  DELIVERY_TERM_1
  PERPETUAL_TERM
  TERMIN_COMMU
  TERMIN_NOTICE_LEAD_TIME
  LIMIT_VALUE
  MINIMUM_VALUE
  PAYTERMS

I have tried with the below logic to simplyfiying the solution for customize the standered flow as per our requirement but it is not working

Step 1: Checking the Master Agreement existence based on the DOCUMENT_DESCRIPTION with the Query IAPI

Step2: If Master Agreement presents then getting the UNIQUE_DOC_NAME value for the Master Agreement. Passing the same Unique Document Name to the

doc.getFieldMetadata("UNIQUE_DOC_NAME").set(doc, uniqueDocName);

Expected result is It should accept the Unique Document Name and should go to the SAP Standered functionality to update the existing Master Agreement with the changes from the RFX data.
  
But It is throwing an error This value is already used by another Master Agreement.

I am trying to customize the standered functionality for import the RFX Data with below beanshell script.

Scripting Context: Import life cycle event
Target: Process Row

try {
  HashMap filterPrompts = new HashMap();
  filterPrompts.put("docdescription",agreementDocmentDescription);
  queryExec = IapiQueryExecFactory.createQueryExec(session, "Document   Description from FCI_Contracts");
  paramSet= queryExec.getParameterSet(session, "Document Description from FCI_Contracts");
  resultSet = queryExec.execute(filterPrompts);
  metaData = resultSet.getMetaData();
  while (resultSet.next()){
  documentDescription = resultSet.getString(0);
  uniqueDocName = resultSet.getString(1);
  Logger.info(logMsg.setLogMessage("Document Description from FCI_Contracts"+documentDescription+"Unique Document Name"+uniqueDocName));
  // Getting the field values from the uploading document
  currency = doc.getFieldMetadata("CURRENCY").get(doc);
  displayName = doc.getDisplayName();
  location =  doc.getLocation();
  ObjectReferenceIfc company = (ObjectReferenceIfc) doc.getCompanyRef();
  ObjectReferenceIfc contact = (ObjectReferenceIfc) doc.getContactRef();
  ObjectReferenceIfc businessUnit = (ObjectReferenceIfc) doc.getOrganizationalUnitRef();
  ObjectReferenceIfc extCat = (ObjectReferenceIfc) doc.getExtCatRef();
  ObjectReferenceIfc intCat = (ObjectReferenceIfc) doc.getIntCatRef();
  ObjectReferenceIfc payTerms = (ObjectReferenceIfc) doc.getPayTermsRef();
  ObjectReferenceIfc vendor = (ObjectReferenceIfc) doc.getVendorRef();
  ObjectReferenceIfc docoweruser = (ObjectReferenceIfc) doc.getDocumentOwnerUserReference();
  ObjectReferenceIfc docType = (ObjectReferenceIfc) doc.getDocTypeReference();
  PriceIfc limitValue= (PriceIfc) doc.getLimitValue();
  SysDateIfc effDate= (SysDateIfc) doc.getEffectiveDate();
  SysDateIfc expDate= (SysDateIfc) doc.getExpirationDate();
  LocalizedObjectReferenceIfc status = (LocalizedObjectReferenceIfc) doc.getStatusRef();
  Boolean perpetualTermFlag=doc.getPerpetualTerm();
  if (documentDescription!=null && documentDescription.equals(agreementDocmentDescription))
  {
     if(uniqueDocName!=null)
     {
     // Get the Agreement Object
     try{
    ContractIBeanHomeIfc contractHome =(ContractIBeanHomeIfc) IBeanHomeLocator.lookup(session, ContractIBeanHomeIfc.sHOME_NAME);
    ContractIBeanIfc contractdoc =(ContractIBeanIfc) contractHome.findForEditByUniqueDocName(uniqueDocName);
    contractHome.upgradeToEdit(contractdoc);
    contractdoc.setDisplayName(displayName);
    contractdoc.setCurrency(currency);
    contractdoc.setCompanyRef(company);
    contractdoc.dbSetVendorRef(vendor);
    contractdoc.dbSetContactRef(contact);
    contractdoc.setLocation(location);
    contractdoc.setOrganizationalUnitRef(businessUnit);
    contractdoc.setExtCatRef(extCat);
    contractdoc.setIntCatRef(intCat);
    contractdoc.setPayTermsRef(payTerms);
    contractdoc.setDocumentOwnerUserReference(docoweruser);
    contractdoc.setLimitValue(limitValue); 
    contractdoc.setEffectiveDate(effDate);
    contractdoc.setExpirationDate(expDate);
    contractdoc.setStatusRef(status);
    contractdoc.setPerpetualTerm(perpetualTermFlag);
    contractHome.save(contractdoc);
    contractHome.downgradeToView(contractdoc);
    contractHome.cancel(doc);
     }
   catch(ApplicationException ae) {
    logMsg.setLogMessage(ae.toString());
    logMsg.setException(ae);
    Logger.error(logMsg);
   }
     }
     }
  }
  }
  catch(ApplicationException ae) {
   logMsg.setLogMessage(ae.toString());
   logMsg.setException(ae);
   Logger.error(logMsg);
  }
  finally {
  resultSet.close();
  }

In the above approach i could not find all the getter and setter methods for all the RFX columns below are the examples

  BUYER_SEARCHABLE
  COLLABORATOR_1
  COLLABORATOR_2
  COLLABORATOR_3
  DATE_TO_PUBLISH
  DOC_TYPE
  GROUP_1
  GROUP_2
  GROUP_3
  MINIMUM_VALUE
  RENEWAL_REMINDER
  RETENTION_DATE
  TERMIN_COMMU
  TERMIN_NOTICE_LEAD_TIME
  VENDOR_VISIBLE

Please suggest me: Is this approach is correct approach or do we have any alternate way to customize for Importing the Inbound RFX Data?

Regards,
Ravindra

[Former Member]

Hi Ravindra,

The 2nd approach to achieve copying fields from RFx to Master Agreement seems feasible to me.

You can get the collaborators of RFx using interface CollaboratorIBeanIfc.

doc.getCollaborators() will give you collection of collaborators used in RFx. You can set the roles and all the columns to Master Agreement accordingly using the getter and setter methods provided.

Regarding groups,  Interface GroupIBeanIfc and its Home IBean could be helpful to get and set them.

The other fields like Renewal Reminder Date, Retention Date, Termination notice lead time, Termination Communication date, Buyer Searchable, Date to Publish are not available in RFx. you can manually fill the values once MA is created or you can set some default values of these fields as setter methods are available if your business allows.

Hope this would reduce your efforts a bit.

Regards,

Kumud