Writing an XML Publisher REST API

You might want to add a Documents block to provide the user with a dynamically generated document list.

Before creating the REST API, you need several items:

• A SQL statement to return the XML and use as the actual data source for the XML Publisher template. See the section, Write your SQL statement.
• An XML Publisher template which expects the XML data in the structure defined in the SQL statement, typically an RTF or Excel document.
• The XML Publisher Data Definition and Template defined through the XML Publisher Administrator responsibility.

Write your SQL statement

You can use the Oracle native XML functions to create an XML document in SQL, such as XMLElement, XMLForest and XMLAgg.

Here is an example of a SQL statement that aggregates person records:

           SELECT XMLElement
                   ("PER"
                    ,XMLElement
                     ("LIST"
                     ,XMLAgg
                       (XMLElement
                         ("PERSON"
                         ,XMLForest
                           (papf.first_name
                           ,papf.last_name
                           ,papf.email_address
                           )
                         ) ORDER BY papf.last_name
                       )
                     )
                   ) xml_output
          FROM     per_all_people_f papf
          WHERE    papf.person_id > 123 AND papf.person_id < 456;

Some essential points you must observe:

• The SQL statement must return exactly one row. This example uses XMLAgg to aggregate multiple rows into a single XML document.
• The SQL statement must return only one column. This example returns the xml_output column containing the entire XML document.
• The column returned must include a column alias. This example calls it xml_output, but it can be called anything you like.
• When using XMLAgg, the ORDER BY statement goes within the XMLAgg clause and not in the typical place after the WHERE clause. XMLAgg will ignore ORDER BY statements after the WHERE clause.

On a Vision environment, this provides the following output, which is limited to just two people:

<?xml version="1.0" encoding="UTF-8"?>
<PER>
   <LIST>
      <PERSON>
         <FIRST_NAME>Henry</FIRST_NAME>
         <LAST_NAME>Abbott</LAST_NAME>
         <EMAIL_ADDRESS>HABBOTT</EMAIL_ADDRESS>
      </PERSON>
      <PERSON>
         <FIRST_NAME>Robert</FIRST_NAME>
         <LAST_NAME>Zon</LAST_NAME>
         <EMAIL_ADDRESS>nobody@localhost</EMAIL_ADDRESS>
      </PERSON>
   </LIST>
</PER>

This example hard-codes Ids but you'll likely want to pass some bind parameters instead. You have a choice of using path parameters and request parameters. For example, the below SQL statement has been re-written to include an example of each:

           SELECT XMLElement
                   ("PER"
                    ,XMLElement
                     ("LIST"
                     ,XMLAgg
                       (XMLElement
                         ("PERSON"
                         ,XMLForest
                           (papf.first_name
                           ,papf.last_name
                           ,papf.email_address
                           )
                         ) ORDER BY papf.last_name
                       )
                     )
                   ) xml_output
          FROM     per_all_people_f papf
          WHERE    papf.person_id = {pathParam.personId}
          AND      nvl(to_date({requestParam.effectiveDate}, 'YYYY-MM-DD'), trunc(sysdate))
                   BETWEEN papf.effective_start_date AND papf.effective_end_date;

This example uses a path parameter to restrict the output to just a single person Id and accepts an optional request parameter to filter the effective date, filtered on today's date by default.

The syntax for a path parameter is {pathParam.yourParameterNameInCamelCase}. The syntax for a request parameter is

{requestParam.yourParameterNameInCamelCase}. You can use as many path parameters and request parameters as you like. While this syntax won't work in Oracle SQL Developer (you'll need to replace it with hard-coded values to test), it will work when placed inside a REST API.

If your SQL is very complicated, you can choose to call a PL/SQL function to return the XML instead. For example:

          SELECT   xx_my_package.my_function
                     ({pathParam.personId}
                     ,{requestParam.effectiveDate}
                     ) xml_output
          FROM     dual;

Creating your REST API

When you have completed the previous steps, you can create your REST API.

To create your REST API:

1. Navigate to Applaud Administrator: Setup: REST APIs: Add
2. Set the ServiceName to a unique value, such as W8Ben
3. Set the Path to a unique path that no other REST API uses. For example, custom/w8-ben/{personId}. This Path must include all path parameters you have used in the SQL statement, and the name of the parameters, including the case, must match exactly. If you have multiple path parameters, try to separate them so that the path parameters are not next to each other. For example, custom/w8-ben/{personId}/by-year/{year} instead of custom/w8-ben/{personId}/{year}. Do not attempt to include two path parameters in the same part. For example, custom/w8-ben/{personId}_{year} is not a good idea and may not work.
4. Request parameters do not need to be defined at all here.
5. You can leave the Consumes and Produces to the default of application/xml.
6. Leave it Enabled.
7. Save.

To create your REST API method:

1. Hit Add under the Methods block
2. For the Method Name, prefix the ServiceName you used above with the action, for example, GetW8Ben.
3. The method is GET.
4. The Path, Consumes, and Produces can be left default.
5. The Provider is SQL.
6. Leave Remove Root Element checked.
7. Paste in your SQL and remove the semi-colon at the end.
8. Find your XML Publisher Data Definition that you defined earlier.
9. Save.

Your REST API is now ready to test and you can test it by running the URL in your browser. Use this URL to test it:

<host>/OA_HTML/applaud/api/<yourPathWithParametersSubstituted>?_accept=application/pdf

For example:

http://ebsdev.applaudsolutions.com:8000/OA_HTML/applaud/api/custom/w8-ben/123?_accept=application/pdf

To use it with request parameters:

http://ebsdev.applaudsolutions.com:8000/OA_HTML/applaud/api/custom/w8-ben/123?_accept=application/pdf&effectiveDate=2017-12-31

An example with multiple request parameters:

http://ebsdev.applaudsolutions.com:8000/OA_HTML/applaud/api/custom/w8-ben/123?effectiveDate=2017-12-31&anotherRequestParam=someValue

You should see the dynamically generated XML publisher document if it's worked successfully. If it hasn't worked, please see Troubleshooting.

Adding in security

Your new REST API, by default, can be run by any logged-in user. In the previous examples, you could see other people's W8 documents by swapping the person Id in your browser's address bar.

You must add in security for all of your custom REST APIs. There are two ways you can do this:

1. Hard-coded

In your SQL, join session variables to restrict the results returned. For example:

         ...
         ) xml_output
          FROM     per_all_people_f papf
          WHERE    papf.person_id = {pathParam.personId}
          AND      nvl(to_date({requestParam.effectiveDate}, 'YYYY-MM-DD'), trunc(sysdate))
                   BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND      papf.person_id = fnd_global.employee_id;

This example uses the fnd_global.employee_id variable to restrict the results to the logged-on person.

2. Roles-based access

If you want to have more flexible security, you can restrict the results using role-based access control, and this is best explained with an example:

...
         ) xml_output
          FROM     per_all_people_f papf
          WHERE    papf.person_id = {pathParam.personId}
          AND      nvl(to_date({requestParam.effectiveDate}, 'YYYY-MM-DD'), trunc(sysdate))
                   BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND      xxas_com_sec_pkg.test_instance('XXX_DT_W8_BEN', {pathParam.personId}) = 'Y';

This tests that the logged-on user has access to the XXX_DT_W8_BEN Permission for the given Person Id. In this example:

• The permission XXX_DT_W8_BEN links to the Data Object Applaud HR Person
• The Data Object has a single primary key column defined called PERSON_ID
• The Data Object uses the PER_ALL_PEOPLE_F database table
• The permission XXX_DT_W8_BEN is included in a permission set called XXX_DT_W8_BEN
• The Permission Set is granted to the Applaud Employee Self Service Role role against an Instance Set that checks &TABLE_ALIAS.person_id = fnd_global.employee_id.

This setup produces identical results to that of the hard-coded example. However, you can create additional grants through configuration to easily grant wider access without changing the REST API. For example, you could allow managers to see an employee's W8 ben or allow an HR Professional to see all employees' W8 ben.

Troubleshooting

When you first build a REST API to create a dynamically-generated XML Publisher document, the chances are it won't work the first time!

The first thing to check is that the XML generates without error and in the structure, you'd expect. You can verify this by changing the _accept request parameter from application/pdf to application/xml. For example:

http://ebsdev.applaudsolutions.com:8000/OA_HTML/applaud/api/custom/w8-ben/123?_accept=application/xml

When you run this URL, it will show you the dynamically-generated XML that feeds the XML publisher document and not the document itself.