Oracle Fusion: Development of Custom BI Publisher Report

This blog is for Oracle Fusion Applications Beginners

Steps to develop Custom BI Publisher report in Oracle Fusion Cloud Applications

Step 1 : Creation of Data Model

Navigation : Navigator --> Reports and Analytics

Click on Browse Catalog

Page will be redirected to Business Intelligence Home Page

 

Click on New Icon which is available on let side of BI home Page

In Diagram tab click New Data Set -- SQL Query

Enter details

Name : Can give as per your Naming Standards (E. g SP_JOUR_DM)
Data Source : This can be selected based fusion DB Schema which we would like to access
Note :
ApplicationDB_FSCM ( Financial and Supply Chain Management )
ApplicationDB_HCM ( Human Capital Management )
ApplicationDB_CRM ( Customer Relationship Management )

Type of SQL: This is can based on Type of SQL which using for report

Standard SQL :Standard SQL used for normal SELECT statements interpreted to understand database schema
Procedure Call : query type to call a database procedure
To use REF cursor in Oracle BI Publisher :
Use this query type to issue SQL statements that can include the following:
Cursor statements that return nested results sets
Functions returning REF cursors
To use REF cursor in Oracle BI Publisher

In SQL Query Section we can provide Query If readily available . Otherwise can use Query Builder to Build Query for report

 

Click Ok . Then Save Data Model

Enter Details of Data Model

Click on Data Tab to save sample data ( This sample data is required to created layout)

Step 2 : Creation of Report

Click on Create Report Which is available in BI Home Page

Default Data Model will selected Click On Next

Select Page Options : Portrait
           Layout : Table

Click next

Drag Fields from Data Source --> Click Next

Click on Finish to Save report and View Output

We can report output in Interactive Dashboard

To View or Download output in different format . Click on View report icon which is available in extreme right corner .

Oracle Fusion : Supplier Bank Account REST APIs

(GET) External Payees

The below REST API is used to check whether the external payee exists or not.

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/paymentsExternalPayees?finder=ExternalPayeeSearch;SupplierSiteIdentifier={SupplierSiteIdentifier},PayeePartyIdentifier={PayeePartyIdentifier},Intent=Supplier

{SupplierSiteIdentifier} represents the Supplier Site Id
{PayeePartyIdentifier} represents the Supplier Party Id

(GET) External Bank Account Details

The below REST API is used to check whether the external bank account exists for the supplier or not.

Endpoint:

1.To fetch all bank accounts of the supplier

https://ERPCloudInstance/fscmRestApi/resources/latest/externalBankAccounts?q=VendorId={VendorId}&finder=SearchExternalBankAccount;Intent=Supplier

{VendorId} represents the Supplier Id

2. To check for particular bank account using the attributes bank name , branch name, account number

https://ERPCloudInstance/fscmRestApi/resources/latest/externalBankAccounts?finder=SearchExternalBankAccount;BankName={BankName},BankBranchName={BankBranchName},BankAccountNumber={BankAccountNumber},Intent=Supplier

In order to check for bank account exits for the supplier, the bank account number masking should be kept as Display all digits in Manage System Security Options

For security reasons the bank number is kept masked , So querying the supplier bank account details w.r.t to bank account number is not possible.

(POST) Create External Bank Account and Bank Account Owners

The below REST API is used to create external bank account for the supplier.

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/externalBankAccounts

Sample Request Payload:

{
"BankAccountNumber": "123456676765",
"CountryCode": "US",
"BankName": "DUMMY",
"BankNumber": null,
"BankBranchName": "DUMMY_0999",
"BankBranchNumber": null,
"CurrencyCode": "USD",
"AccountType": "SAVINGS",
"AllowInternationalPaymentIndicator": "N",
"Intent": "Supplier",
"PartyId": 23231231132,
"BankAccountName": "DDP_011",
"accountOwners": [
{
"AccountOwnerPartyIdentifier": 23231231132,
"Intent": "Supplier"
}

]}

{AccountOwnerPartyIdentifier} and {PartyId} from the above request payload represents Supplier’s Party Id

(GET) Instrument Assignment

Rest API to get instrument assignments. 

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/instrumentAssignments?finder=PaymentInstrumentAttributes;Intent=Supplier,PaymentPartyId={PaymentPartyId}

{PaymentPartyId} – External {PayeeId} from the call GET External Payee Rest API.

(POST) Create Instrument Assignment

The below REST API is used to create instrument assignment to the created bank account.
In order to create Instrument Assignment, we need to setup our user as active worker.

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/instrumentAssignments

Request Payload :

{
"PaymentPartyId": 333333,
"PaymentFlow": "DISBURSEMENTS",
"PaymentInstrumentId":122222223,
"PaymentInstrumentType": "BANKACCOUNT",
"Intent": "Supplier"
}

{PaymentPartyId} - represents external payee id from the REST call GET External Payees (which was derived from corresponding level (supplier/site) the bank is attached to the supplier )

{PaymentInstrumentId} – is the {BankAccountId} from the REST call GET External Bank Accounts.

Oracle Fusion : Query to fetch privileges and roles assigned to the user

Privileges:

/*Query to fetch privileges assigned to the user*/

SELECT DISTINCT u.user_display_name AS UserName,

  rt.role_name ,

  pt.name privilege_name

FROM ASE_PRIVILEGE_B p ,

  ASE_PRIVILEGE_TL pt,

  ASE_PRIV_ROLE_MBR m ,

  ASE_ROLE_B r ,

  ASE_ROLE_TL rt,

  ASE_USER_TL u ,

  ASE_USER_ROLE_MBR rm

WHERE 1=1

AND ( SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE

OR p.EFFECTIVE_END_DATE IS NULL)

AND ( SYSDATE BETWEEN r.EFFECTIVE_START_DATE AND r.EFFECTIVE_END_DATE

OR r.EFFECTIVE_END_DATE IS NULL)

AND p.PRIVILEGE_ID       = m.PRIVILEGE_ID

AND m.role_id            =r.role_id

AND u.LANGUAGE           = 'US'

AND rm.user_id           = u.user_id

AND rm.role_id           = r.role_id

AND pt.PRIVILEGE_ID      = p.PRIVILEGE_ID

AND pt.LANGUAGE          = 'US'

AND rt.role_id           = r.role_id

AND rt.LANGUAGE          = 'US'

AND ( SYSDATE BETWEEN m.EFFECTIVE_START_DATE AND m.EFFECTIVE_END_DATE

OR m.EFFECTIVE_END_DATE IS NULL)

AND ( SYSDATE BETWEEN rm.EFFECTIVE_START_DATE AND rm.EFFECTIVE_END_DATE

OR rm.effective_end_date IS NULL)

AND u.user_display_name   = :p_user_name

ORDER BY 1,2,3

Roles:

/*Query to fetch roles assigned to the user*/

SELECT pu.user_id,

  pu.username,

  ppnf.full_name,

  prdt.role_id,

  prdt.role_name,

  prd.role_common_name,

  prdt.description,

  TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,

  TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,

  prd.abstract_role,

  prd.job_role,

  prd.data_role,

  prd.duty_role,

  prd.active_flag

FROM per_user_roles pur,

  per_users pu,

  per_roles_dn_tl prdt,

  per_roles_dn prd,

  per_person_names_f ppnf

WHERE 1                           = 1

AND pu.user_id                    = pur.user_id

AND prdt.role_id                  = pur.role_id

AND prdt.language                 = USERENV ('lang')

AND prdt.role_id                  = prd.role_id

AND NVL (pu.suspended, 'N')       = 'N'

AND ppnf.person_id(+)             = pu.person_id

AND ppnf.name_type(+)             = 'GLOBAL'

AND pu.active_flag                = 'Y'

AND NVL (pu.start_date, SYSDATE) <= SYSDATE

AND NVL (pu.end_date, sysdate)   >= sysdate

AND pu.username                   = :p_user_name 

Oracle Cloud Supplier Portal View Access - Privileges

The following are the privileges required for a user to have view access of the supplier portal.

1. Access Suppliers Work Area Overview
2. Search Supplier
3. Search Supplier Assessment
4. Search Supplier Qualification
5. View Supplier
6. View Supplier Address
7. View Supplier Assessment
8. View Supplier Attachment
9. View Supplier Business Classification
10. View Supplier Contact
11. View Supplier Contact Change Request
12. View Supplier Products and Services Categories
13. View Supplier Profile Change Request
14. View Supplier Qualification
15. View Supplier Qualification Initiative
16. View Supplier Qualification Question
17. View Supplier Registration Bank Account
18. View Supplier Registration Request
19. View Supplier Site
20. View Supplier Site Assignment
21. View Supplier Site Invoicing Information
22. View Supplier Site Purchasing Information
23. View Supplier Site Receiving Information
24. View Supplier Transaction Tax