Sales Price List (Item Price) Query

 SELECT PUOMPRICE.PRICE_LIST_ID LISTNUMBER,
       ESI.INVENTORY_ITEM_ID   ITEMID,
       ESI.ITEM_NUMBER         ITEMNUMBER,
       PUOMPRICE.BASE_PRICE    PUOMPRICE,
       SUOMPRICE.BASE_PRICE    SUOMPRICE
FROM   EGP_SYSTEM_ITEMS_B ESI,
       INV_ORG_PARAMETERS IOP1,
       (SELECT QPLB.PRICE_LIST_ID,
               QPLI.ITEM_ID,
               QPLI.PRICING_UOM_CODE,
               QPLC.BASE_PRICE
        FROM   QP_PRICE_LISTS_ALL_B QPLB,
               QP_PRICE_LIST_ITEMS QPLI,
               QP_PRICE_LIST_CHARGES QPLC
        WHERE  1 = 1
               AND QPLB.PRICE_LIST_ID = QPLI.PRICE_LIST_ID
               AND QPLC.PARENT_ENTITY_ID (+) = QPLI.PRICE_LIST_ITEM_ID
               AND QPLB.PRICE_LIST_ID = :PRICE_LIST_ID
               AND ( QPLC.LAST_UPDATE_DATE BETWEEN :LASTDATE AND :ICSDATE
                      OR QPLI.LAST_UPDATE_DATE BETWEEN :LASTDATE AND :ICSDATE
                      OR QPLB.LAST_UPDATE_DATE BETWEEN :LASTDATE AND :ICSDATE ))
       PUOMPRICE,
       (SELECT QPLB.PRICE_LIST_ID,
               QPLI.ITEM_ID,
               QPLI.PRICING_UOM_CODE,
               QPLC.BASE_PRICE,
               ( QPLI.ITEM_ID
                 ||QPLI.PRICING_UOM_CODE ) FORJOIN
        FROM   QP_PRICE_LISTS_ALL_B QPLB,
               QP_PRICE_LIST_ITEMS QPLI,
               QP_PRICE_LIST_CHARGES QPLC
        WHERE  1 = 1
               AND QPLB.PRICE_LIST_ID = QPLI.PRICE_LIST_ID
               AND QPLC.PARENT_ENTITY_ID (+) = QPLI.PRICE_LIST_ITEM_ID
               AND QPLB.PRICE_LIST_ID = :PRICE_LIST_ID
               AND ( QPLC.LAST_UPDATE_DATE BETWEEN :LASTDATE AND :ICSDATE
                      OR QPLI.LAST_UPDATE_DATE BETWEEN :LASTDATE AND :ICSDATE
                      OR QPLB.LAST_UPDATE_DATE BETWEEN :LASTDATE AND :ICSDATE ))
       SUOMPRICE
WHERE  1 = 1
       AND ESI.ORGANIZATION_ID = IOP1.ORGANIZATION_ID
       AND IOP1.ORGANIZATION_CODE = 'MASTER ORG'
       AND ( ESI.INVENTORY_ITEM_ID
             ||ESI.SECONDARY_UOM_CODE ) = FORJOIN (+)
       AND PUOMPRICE.ITEM_ID = ESI.INVENTORY_ITEM_ID
       AND PUOMPRICE.PRICING_UOM_CODE = ESI.PRIMARY_UOM_CODE
ORDER  BY 1,
          2 

Oracle Visual Builder Add-In for Excel

A detailed guide for configuring The Oracle Visual Builder Add-in for Microsoft Excel, enabling business users to modify data retrieved from REST services. This post covers the installation process, practical use cases, and limitations.

Business Use Case:

• The Oracle Visual Builder Add-in for Excel is particularly valuable for executing rapid bulk updates in scenarios where FBDI / File Import Management lacks support for UPDATE operations.
• Additionally, it provides a straightforward method to update values to NULL, a task that is typically not feasible using FBDI in UPDATE mode.
• By leveraging Excel’s templates, users can efficiently perform bulk data validation and updates, significantly reducing the potential for errors and minimizing the time required compared to manual data manipulation across multiple systems.

Installation Steps:

Download the most recent version from the below Oracle Path:

Download Link

Sign-in with your Oracle account, download the file and install it.

Enable Add-In for Excel

Build Data Template

Create the Excel template to perform data manipulations, such as for contracts.

Rest APIs: https://docs.oracle.com/en/cloud/saas/sales/faaps/op-contracts-post.html

(Use the most recent version)

Web-Address Formation: Oracle environment URL + REST API + describe

https://<your_env>.fa.ocs.oraclecloud.com

+

/fscmRestApi/resources/11.13.18.05/contracts (Create Contracts)

+

describe

Web-Address:

https://<your_env>.fa.ocs.oraclecloud.com//fscmRestApi/resources/11.13.18.05/contracts/describe

Sign in using your Oracle application login credentials. Users must have the appropriate roles and access permissions to perform the related transactions, including the necessary REST API roles and access.

Click → Next

Identify and select the required business objects

Choose the template layout and click Next

Choose the fields selection, and click Next

Choose the required business objects and click Next

Click → Finish

Explore Designer Layout (Options)

• General
• Columns
• Download
• Advanced

Explore Manage Catalogs

Explore Advanced options → Edit Service Host (Entering the Oracle target environment URL)

Create sample download parameters and retrieve the data. For more details, see the image below:

Perform validation via the UI

Update any field as an example, such as the end date.

Click → Upload Changes

Review the log messages!

Validate the change in UI

The contract data has been updated successfully.

Limitations

• Most API templates have a row limit of 500, which may cause failures or delays when processing thousands of rows.
• In such cases, multiple sheets or templates with suitable download parameters may be required. Note that the download parameters can be up to approximately 5,000 characters in length.

How to set up a Page Break in OTBI Analysis

Creating a Page Break in the Layout Section Properties:

1. Add or select the column you want to use as a page break in the Sections area.
2. In the 'Insert Page Break' properties, select the same column.

Example - Supplier Invoice Analysis

(Page Break By Supplier)

Validate the Report Output in 'Printable PDF'

Configuring Enterprise Contract Approvals Using DFF Attributes

Oracle has provided few documentations, but the guide below simplifies the logic for easier understanding.

Reference:

• Enterprise Contract Approval Setup Based On DFF Attribute As Attribute1,Attribute2 (Doc ID 2234904.1)
• How To Send Contracts For Approval To The Name Value In A DFF Descriptive Flexfield using Supervisory List builder? (Doc ID 2665380.1)

DFF Attributes in IF Clause

Step 1: Define the DFF according to your requirements.

Step 2: Run the "Start Synchronization" process.

Step 3: Create the approval rules based on your requirements.

DFF Attributes in THEN Clause

Step 1: Define the DFF according to your requirements.

Step 2: Run the "Start Synchronization" process.

Step 3: Config Approval Rule

Example:
HierarchyBuilder.getPrincipal(ContractHeaderFlexfield.legalReviewer,-1,"","")

Step 4: Capture DFF Values in the Contract & Submit for approval

Step 5: Validate the approvers