We described earlier some of the initial operations we need to do to integrate OPA with Oracle eBS. Below you see an overview of the rest of the operations. So we have a model for OPA, which we create in the model designer. A model needs to be deployed to be usuable as runtime.
In the model we invoke the GetMetaData to get the data structure and the valuesets we like to use in our interview.
So each time something changes in your data structure or valuesets, you need to do a GetMetaData in the model and deploy the model to the runtime version.
During runtime you have two options
1. Start
2. Resume
These options use their own URLs to start the interview and also invoke other services. The start invokes the LoadRequest, where the resume invokes the GetCheckPoint to resume an existing interview from the point you saved.
LoadRequest
The load request loads initial data from your datamodel, which can be used as input parameters for the interview. When you called the interview we can pass an initial parameter in the URL as well. We used that to indicate the user, responsibility and a unique ID to identify the record in our table. Of course you want to encode those parameters, so we used DBMS_OBFUSCATION_TOOLKIT.Desencrypt to encrypt these parameters into one connection string.
When you then get the LoadRequest, it passes back your initial parameter so you can decrypt it and identify the user, authorization and the record we are creating/updating.
First thing we do then is a fnd_global.apps_initialize, because the user calling our webservice through the Integrated SOA Gateway is a generic user.
Our LoadRequest procedure looks something like
procedure LoadRequest
(
root IN VARCHAR2
, region IN VARCHAR2
, language IN VARCHAR2
, timezone IN VARCHAR2
, request_context IN xxgr_opa_t_RequestContext
, seedDataDescription IN xxgr_opa_t_Tables
, loadData OUT xxgr_opa_t_LoadData
, error_msg OUT VARCHAR2
, Status OUT varchar2
)
Here the seedDataDescription contains a list of entities and fields the interview is requesting from us. So you have to check which specific fields the service wants and pass values for those back. You cannot pass more values (service will fail).
We also use a translation for booleans, because Y/N values in the database should be translated to true/false values for the service and return type boolean, where we cannot use booleans in our data structures directly.
What we actually did is draw a sequence number and pass that as parameter on the interview. As soon as we got the LoadRequest back we created our record in the database with that sequence (because we were creating entities using the interview and until we get some feedback back from the interview we do not actually need the record).
During SetCheckpoint and SaveRequest we continue processing our record.
Information we pass on the load is stuff like the name of the user that called the interview, his organization, etc.
Drawback is that we can return an error to the webservice, but OPA cannot handle that. It will not show the error message we send back (just a generic error).
SetCheckPoint
During your interview you can save the data so you can resume later. Those are called checkpoints. The hub call call the SetCheckpoint to save a base64 encoded zip file of the interview. That zip file contains an XML containing variables entered in the interview; so if you want to you could unzip and decode the information and actually store structured data in between save points.
procedure SetCheckpoint
(
request_context IN xxgr_opa_t_RequestContext
, checkPointData IN xxgr_opa_r_CheckPoint_Data
, checkpointId OUT varchar2
, error_msg OUT varchar2
, Status OUT varchar2
)
So setting a checkpoint is merely saving the blob data given our context (request_context). That contains again the parameter(s) we passed to the initial URL, which is our encrypted key with user information.
In our specific case we would create purchase requisitions from our interviews, so this was the moment we actually created the requisition and related our interview (using a custom table) to it.
SaveRequest
When you are done with your interview you can submit the data using a save request operation.
procedure SaveRequest
(
root IN VARCHAR2
, region IN VARCHAR2
, language IN VARCHAR2
, timezone IN VARCHAR2
, request_context IN xxgr_opa_t_RequestContext
, submitData IN xxgr_opa_t_submit_data
, attachments IN xxgr_opa_t_attachments
, auditReport IN xxgr_opa_t_audit_report_list
, updateData OUT xxgr_opa_t_UpdateData
, error_msg OUT VARCHAR2
, Status OUT varchar2
)
This is the most complex operation, because now we get all the structured data in the submitData including attachments and auditreports.
There are some restrictions on attachments in the service. You can restrict the size of files in OPA, but the SOA Gateway also may have its own restrictions. We also had a service bus in between with memory restrictions, so we had a limit of max 40MB on files. But note that on each setcheckpoint it would send any attachments in the base64 encoded zip that you already uploaded. So it's good practice to add your attachments as late as possible in the interview to avoid a lot of data traffic.
The submitData contains two parts
- The input fields
- Request for output fields
The latter is a request after the submit (which you can use in the OPA model) to pass back some information. So we capture the fields that are requested to pass them back later after we are done (for example to pass a requisition number).
<<OutputFields>>
FOR i in 1 .. submitData(l_det_ind).submitRow(1).outputfield.count
LOOP
L_Outputs (submitData(l_det_ind).submitRow(1).outputfield(i).name) := submitData(l_det_ind).submitRow(1).outputfield(i).name;
END LOOP OutputFields;
Then we loop through all the input fields, validate the input and store the data. Of course we could store the names as indexes in a table, but we still need to identify per field what we want to do with it.
<<InputFields>>
FOR i in 1 .. submitData(l_det_ind).submitRow(1).inputfield.count
LOOP
IF submitData(l_det_ind).submitRow(1).inputfield(i).name = 'DESCRIPTION'
AND submitData(l_det_ind).submitRow(1).rowAction = 'update'
THEN
l_description := submitData(l_det_ind).submitRow(1).inputfield(i).data_val;
END IF;
Note that we can have multiple entities (l_det_ind), with multiple rows and multiple fields. In our specific case we only had one main record, but multiple sub records. So we used submitRow (1) here, but otherwise we would have used loop.
Attachments
Now we get 2 types of attachments. You get the main report IN the data and a separate attachments parameter.
submitData(l_det_ind).submitRow(1).attachments
contains the attachments on our main entity.
So finally we validate our input, update our record and return a message back. This message can be shown, if you use the LoadAfterSubmit option in OPA. It loads information you can give back, so we can show error and warning messages if necessary.
In our current release you can only submit once. After that you need to close the interview and re-open it to make changes.
GetCheckPoint
Then finally the get check point operation, which is used if you resume an interview. We simply read the base 64 encoded string (zip file) and pass that back to the webservice.
procedure GetCheckpoint
(
request_context IN xxgr_opa_t_RequestContext
, checkPointData OUT xxgr_opa_r_CheckPoint_Data
, error_msg OUT varchar2
, Status OUT varchar2
)
So this is pretty straightforward. The only thing we also do, on all operations, is check whether the user is allowed to do this. For example you cannot do a Load if you aren't logged in right now. And you cannot submit if you were not logged in today.
