How to use an ESS Job for the BIP Reports' ETEXT output distribution according to predefined bursting profile

I must admit: there are number of web resources that already address similar issues. However, I intend to delve into a rather specific case that extends the functionality that has been previously presented.

We need a custom BIP reports' ETEXT output to be distributed via email (or directly to FTP) according to the predefined bursting profile. This BIP report has several parameters thus tanking the Scheduled OTBI Job creation option off the table. We will have to create a new custom ESS Job for this task. 

Let’s break it down a bit:

1.        Create a BIP report with parameters that generates ETEXT output.

I will not waste your time on this trivial task: creating a Data Model + ETEXT template.

The following session by Naresh might be a good reference: https://www.youtube.com/watch?v=x1NYTefDbAg

The only two things worth mentioning are: the bursting profile for email distribution and storing the new reports' path we would require for ESS job creation:

a.        To have an ETEXT file properly delivered, make sure to include the TEMPLATE_FORMAT and OUTPUT_FORMAT parameters inside the bursting query attached to the Data Model, e.g. 'ETEXT' as TEMPLATE_FORMAT, 'text' as OUTPUT_FORMAT.

b.        Make sure to fetch the report path via reports for future usage in ESS jobs definition.

History->Report path:

e.g. /Custom/…/…/BLAH_BLAH_BLAH_report.xdo

2.        Create a new custom ESS Job.

From Setup and Maintenance -> Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications (Manage Job Definitions TAB). 

Another session by Naresh could serve as a good reference, so you are advised to watch it prior to the jobs' creation: https://www.youtube.com/watch?v=uDV3mDXR0bQ

Let's settle for a Job name: Custom ESS Job BLAH BLAH

Save and Close.

3.     The way I see it - the next step is the most critical for task completion:

We create a set of parameters for the ESS Job that are completely “synchronized” with the BIP reports’ parameters.

But first, let's create a LOV source (List of Values) for them (a good reference https://docs.oracle.com/en/cloud/saas/financials/24c/faiac/manage-list-of-values-sources.html#s20059933)

a.    We will locate and edit our ESS Job

 Setup and Maintenance -> Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications.  Locate our ESS job:

select and edit:

b.        In the Manage List of Values Sources TAB feel free to define the custom LOV Sources (be advised to use simple queries):

c.        Back at the Manage Job Definitions TAB create (add) the parameters based on the LOV sources created 

before. Make sure the same names are used!!!

Save+Done

4.        Now you can run the " Custom ESS Job BLAH BLAH " from Tools--> Scheduled Processes.

The ETEXT (txt) output file would be delivered to the email mentioned in the bursting profile.

Reverting Changes of a Published Sandbox in Oracle Fusion

 There are a fair number of answers to this question on the web, the most common: NO, YOU CAN'T.

Once Sandbox has been published, you can't "rewind" it. 

Unfortunately, you may be in a regrettable position where your Page went completely FUBAR due to some ill-advised changes through the Page Composer sandbox you accidentally published. 

E.g., the most common workaround: creating a new Sandbox, and fixing any damage you did in an already published sandbox is not an option. Sometimes, even navigating to the page in question is no longer possible. (been there).

The first "glimpse" of the solution was introduced by Oracle note: 

How can Sandbox Page Customizations be Removed From a Page? (Doc ID 2642342.1)

Regrettably, it's pretty light on technical details and chose to omit crucial info. 

The most complete list of resolution techniques was provided by: 

https://wpsbutton.com/reverting-changes-of-a-published-sandbox

It was originally referenced by Oracles' Community thread:

https://community.oracle.com/customerconnect/discussion/677263/can-we-roll-back-an-published-sandbox-to-revert-back-the-changes

That one is almost perfect: it has all those nice screens with cool KABOOM emoji and greatly elaborates on the technique mentioned in (Doc ID 2642342.1).

Namely, a deletion of the Page customizations' configuration file. The only setback is: that it deletes ANY changes implemented on the Page thus restoring it to a seeded functionality.

Unfortunately, one piece of the "puzzle" is still missing:

Let us assume that following the (Doc ID 2642342.1) directions you created the required new Sandbox with the Page Composer tool and even managed to "corner" the customization file through the Manage Configuration menu, where are all those "lovely" delete buttons I have been promised???

Why can’t I see anything besides the Download?

The next “golden nugget” was discovered during a session with Oracle Support.

Navigate to Setup and Maintenance:

Search for Manage Administrator Profile Values:

Locate the FND_ALLOW_MANUAL_METADATA_UPDATES profile option

To have ALL the “buttons” on the Configuration Screen, the Site value should be set to Yes.

Save the changes, Logout, Login, navigate to the Manage Configuration page and locate the customizations file again:

enjoy

SAOP API to Update Descriptive Flexfield on Financial entities at Oracle Fusion

Lately, we have been required to update several Financial entities' (namely Receivables and Payables)  DFFs. Our need came from the new requirement from the Israel Tax Authority for the AP and AR Invoices to our client. Every invoice will be reported LIVE to the IRS and a unique authorization code will be provided back to the client. Subsequently, Oracles' localization team provided Global DFF segments to store those codes.

Unfortunately, an EDIT part of those DFF segments was entirely missing from the corresponding entities' REST APIs. (I believe there is an improvement request already at Metalink)

Following a short search on the Web, I came upon a SOAP service Oracle provided for that very purpose:

https://docs.oracle.com/en/cloud/saas/financials/23d/oeswf/erpobjectdescriptiveflexfieldupdateservice-d16476e12.html#u30243261

Plus there is a Note at Metalink that provides more technical insight on the matter:

How to Update DFF - Descriptive Flexfields Within an Invoice using updateDffEntityDetails? (Doc ID 2482375.1)

I would like to share step-by-step implementation inside the OIC (Oracle Integration Cloud) we have been using.

1. Confirm the DFFs and Localizations' Context in your system:

Oracle established that the IRS Authorization code would be stored in GLOBAL_ATTRIBUTE2 under the VAT Reporting for Israel regional context:

2. Create a suitable connection in OIC as per Oracles documentation mentioned above:

3. The following is a basic (working!) Request for the API calls in your integrations:

AP Invoice Payload example:

<updateDffEntityDetails xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" 

......... xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">

  <nstrgmpr:Body>

    <tns:updateDffEntityDetails>

      <tns:operationMode>SINGLE</tns:operationMode>

      <tns:object>

        <ns1:EntityName>Payables Invoice</ns1:EntityName>

        <ns1:ContextValue>JE_IL_VAT_REPORTING</ns1:ContextValue>

        <ns1:UserKeyA>1302</ns1:UserKeyA>

        <ns1:UserKeyD>300000026329373</ns1:UserKeyD>

        <ns1:DFFAttributes>{"GLOBAL_ATTRIBUTE2":"234233333333333"}</ns1:DFFAttributes>

      </tns:object>

    </tns:updateDffEntityDetails>

  </nstrgmpr:Body>

</updateDffEntityDetails>

According to Oracles documentation, currently, only the SINGLE mode is supported. Context is an absolute MUST - provide a correct code, if no context is defined use #NULL. 

***UserKeysA and UserKeysD are invoice_number and invoice_id accordingly. 

Make sure to follow the exact syntax as above for DFFAttributes, should you need to update more than one attribute, list them within the brackets separated by a comma: {"ATTRIBUTE1":"BlahBlah", .., "ATTRIBUTE(N)":"BlahBlah"}.

***Following the issue we have been facing lately - multiple AP Invoices bearing the same Invoice_Number (invoices originated from different vendors). Oracle provided more specific instructions regarding data passed to API as per SR 3-36709718271 I logged at Oracle Support:

Please follow the template below for Invoices with the same number:

====

User Key Details for the Payables Invoice Entity Name.

User Key

Details

User Key A Invoice Number *

User Key B Business Unit Name **

User Key C Supplier Name **

User Key D Supplier Number **

User Key E Supplier Site Name **

User Key F Invoice Identifier (Invoice_Id)

User Key G null

User Key H null

* Mandatory

** Needed if there are multiple invoices with the same number

To stay on the SAFE side - populate the Keys from A to E:

<nstrgmpr:Body>

<tns:updateDffEntityDetails>

<tns:operationMode>SINGLE</tns:operationMode>

<tns:object>

<ns1:EntityName>Payables Invoice</ns1:EntityName>

<ns1:ContextValue>JE_IL_VAT_REPORTING</ns1:ContextValue>

<ns1:UserKeyA>Invoice_Number</ns1:UserKeyA>

<ns1:UserKeyB>Organization BU</ns1:UserKeyB>

<ns1:UserKeyC>Vendor Name</ns1:UserKeyC>

<ns1:UserKeyD>Vendor Number</ns1:UserKeyD>

<ns1:UserKeyE>Vendor Site</ns1:UserKeyE>

<ns1:DFFAttributes>{"GLOBAL_ATTRIBUTE2":"2842888888888888"}</ns1:DFFAttributes>

</tns:object>

</tns:updateDffEntityDetails>

</nstrgmpr:Body>

It generally throws exceptions if something is amiss but the best indication of a successful execution is result =1 in Response:

<ns0:updateDffEntityDetailsResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">

  <result xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">1</result>

</ns0:updateDffEntityDetailsResponse>

Naturally, the same syntax would work for the Receivables Invoice entity as well.

enjoy