Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Tuesday, September 24, 2024

Fusion OTBI - Oracle Transactional Business Intelligence Reports Training Manual





















With Oracle Fusion Transactional Business Intelligence embedded analytics, role-based dashboards, and on-the- fly ad hoc reporting capabilities make data access and interpretation easier than ever before. Users are able to see updates in real-time, and their impact, through embedded analytics. For example, during the compensation budgeting process, a manager is able to see a running tally for reference right on the page. This eliminates the guesswork with dashboards that deliver in-line information while you do your work. Pre-delivered operational reports are enriched with robust and flexible ad hoc query capabilities that access real-time transactional data – no need to push data to a separate warehouse or engage the IT department for a custom report: it is easy, and right at your fingertips

Business Intelligence Composer

The Business Intelligence Composer is a simple-to-use wizard that allows organizations to quickly and easily create, edit, or view data without requiring an engineering background. With this capability Human Resource organizations can build and deliver reports that the business needs whenever they need it, without waiting for IT support. It is built for the business user.

Real Time Ad Hoc Query

Traditional ad-hoc reporting solutions require a person to have deep domain in their subject area and an understanding of data objects in the relevant tables they needed to report from.

OTBI hides that complexity and transforms the 9000+ reportable data objects into everyday business terminology. Which means if you are looking to see a worker’s average performance rating you will find the reporting object named as “Worker’s Average Performance Rating”.

This is all done by using the standard Oracle query and reporting tool (OBIEE). OBIEE has end user tools (Answers, Dashboards) to provide an easy-to-use interface for business users to perform current state analysis of their business applications. Constructed queries and reports are executed real time against the transactional schema supported by a layer of view objects.

In OTBI, business users can also easily drill on predefined hierarchies, enable action links, compose a dashboard, schedule a report or dashboard for delivery and export data or reports to a variety of file formats. Fusion users can also easily embed an OTBI report or dashboard in a Fusion page through Personalization

Integrated with Fusion Applications

OTBI is fully integrated with Fusion applications (roles, flex fields, lookups, trees). There is no need for additional implementation steps to map security or provide configuration inputs. OTBI needs minimum setup and is ready for use once Fusion Applications are configured and installed.

We are going to develop the Below OTBI Reports

1. Navigating to Reports and Analytics
2. Roles Required to Run/Create/Modify the OTBI Reports
3. Running Oracle Standard Report
4. Creating Analysis Report for Supplier Details
        a. Adding the new column to the existing Report
        b. Remove the Column from the existing Report
        c. Applying the Prompt (Filter) to the existing Report Column
5. Creating BI Report (Using Data Model) for Purchase Order Details
6. Creating Ad Hoc Report for Expense Details
7. Creating Dashboard Report
8. Creating Dashboard Report with Prompt (Filter)
9. Creating Master-Child Analysis Report with Prompt (Filter)
10. Creating Analysis Report with Multiple Subject Areas

 

1.     Navigating to Reports and Analytics

Login to the application with URL

Enter the User name password and click on the Sign in





 

2.       Once login to the application, we have two options to connect the Analytics report

 

3.       Option 1: Modify the URL to add the analytics

https://XXXXXXXXXXXXXXXXXXXX.com/analytics/





 

4.       Option 2: Select Reports and Analytics under Tools in the Navigator.




































5.       Click on the Browse Catalog















2. Roles Required to Run/Create/Modify the OTBI Reports

BI Consumer Role

The predefined OTBI Transaction Analysis Duty roles inherit the BI Consumer Role. You can configure custom roles to inherit BI Consumer Role so that they can run reports but not author them.

 BI Author Role

The BI Author Role inherits the BI Consumer Role. Users with BI Author Role can create, edit, and run OTBI reports.

 BI Administrator Role

BI Administrator Role is a superuser role. It inherits BI Author Role, which inherits BI Consumer Role.The predefined Sales Cloud job roles do not have BI Administrator Role access.

BI Administrator Role Performs administrative tasks such as creating and editing dashboards and modifying security permissions for reports, folders, and so on.

 

3. Running Oracle Standard Report

On the Reports and Analytics page, under the Contents section, click the Expand icon to the left of Shared Folders and then click the Expand icon to the left of the applicable functional area folder into which you want to drill.  In this example, we are drilling in to Financials and scrolling down to General Ledger and further drilling in to access the General Journals Report.

Nav: Shared Folder àFinancials àGeneral Ledger àJournals àGeneral Journal Report















6.       Click the General Journals Report and then in the pop-out, click the Open link















7.       A tab displaying the General Journals Report Parameters is opened in the pane on the right.  Enter the required parameters and any additional parameters, then click the apply button.

 

8.       Depending upon the Parameters entered, the results are displayed in the region below the Parameters.

  

Please click on the below link for more details:

Fusion OTBI - Oracle Transactional Business Intelligence Reports Training Manual
https://drive.google.com/file/d/1qnXf3K6DFQf9J_x8fmkehNZcTjhyVhTZ



Join the https://t.me/OracleApps88 group or message me at @apps88 (https://t.me/apps88) or (+91) 9059574321 in Telegram, to get more information on Oracle EBS R12/Cloud (Fusion) applications.


Cloud AP to FA – Oracle Cloud Procure to Payables (Payments) to Fixed Assets to General Ledger Flow

 















The PO-AP-FA-GL cycle comprises of the following steps,

01: Create Requisitions

02: Create Purchase Order with Requisition

03: Purchase Order Receive Goods

04: Create Payables invoice with Match PO Lines

05: Invoice Payment to Supplier (Optional)

06: Transfer Payables data to General Ledger (Mandatory)

07: Create Mass Additions

08: Prepare Mass Additions

09: Post Mass Additions

10: Verify the Asset

11: Calculate Depreciation

12: Asset Create Accounting


01: Create Requisitions

Nav: Procurement à Purchase Requisitions



 

Click the More Tasks dropdown and select Request Noncatalog Item.



 

Enter the below requisition details and click on Add to Cart button



 

Requisition added to cart



 

Click on the cart  and click on the Review button



 

Review the requisition details and make sure the Charge Account is an Asset Clearing account



 

Review the details and Click on Submit



 

A requisition will be created and it will be submitted for approval, click on the View PDF button to check the requisition details



 



 

Approve the Requisition



 

02: Create a Purchase Order with Requisition

Once the requisition is Approved create the Purchase order with the requisition

Nav: Procurement à Purchase Orders



 

Click on the tasks and click on the Process Requisitions task from Requisitions or select from procurement quick actions

 

Please click on the below link for more details:

Cloud AP to FA – Oracle Cloud Procure to Payables (Payments) to Fixed Assets to General Ledger Flow
https://drive.google.com/file/d/1lve6IGC83wpZBgZedOz_ssBwHvBFyzkY



Join the https://t.me/OracleApps88 group or message me at @apps88 (https://t.me/apps88) or (+91) 9059574321 in Telegram, to get more information on Oracle EBS R12/Cloud (Fusion) applications.


Friday, September 20, 2024

Cloud O2C – Oracle Cloud Order to Cash (O2C) Cycle Process with Technical Flow


The Order to Cash cycle comprises of the following steps,

01. Create Sales Order

02. Create Pick wave

03. Create Pick Confirm

04. Create Ship Confirm

05. Transfer to Receivables

06. Verify the Transaction

07. Create Receipt and Apply the transaction

08. Transfer Invoice to General Ledger

09. Transfer Receipt data to General Ledger

10. Verify the Journal Batches

 

Step 1: Create Sales Order

Nav: Order Management àOrder Management



Click on the Create Order button or Tasks àCreate Order



 Enter the customer details and Click on Save 


Once save it order number will generate and Order status is Draft



 

Below is the Query:
SELECT

 h.org_id

,hou.name BU_NAME

,h.header_id

,h.order_number

,hp.party_name Cust_name

,hca.account_number Cust_Acct

,h.sold_to_contact_id Contact

,h.ordered_date

,h.customer_po_number PO_NUM

,h.order_type_code

,h.status_code Heder_Status

,h.open_flag

,h.canceled_flag

,h.sold_to_customer_id

,h.transactional_currency_code CURR

,h.source_order_number src_order_num

,h.source_order_system src_order_sys

,h.source_revision_number src_rev_num

,h.orig_sys_document_ref src_order_ref

,bill_hp.party_name Bill_To_Cust

,bill_add.cust_acct_site_use_id Bill_site_use_id

,bill_hl.Address1||' '||bill_hl.Address2||' '||bill_hl.city||' '||bill_hl.county||' '||NVL(bill_hl.STATE,bill_hl.PROVINCE)||' '||bill_hl.Postal_code||' '||bill_hl.COUNTRY  Bill_TO_Addr

,ship_hp.party_name ship_To_Cust

,ship_add.cust_acct_site_use_id ship_site_use_id

,ship_hl.Address1||' '||ship_hl.Address2||' '||ship_hl.city||' '||ship_hl.county||' '||NVL(ship_hl.STATE,ship_hl.PROVINCE)||' '||ship_hl.Postal_code||' '||ship_hl.COUNTRY  ship_TO_Addr

from

 fusion.doo_headers_all_v h

,fusion.hr_operating_units hou

,fusion.hz_parties hp

,fusion.hz_cust_accounts hca

,fusion.doo_order_addresses_v bill_add

,fusion.hz_cust_accounts bill_hca

,fusion.hz_parties bill_hp

,fusion.hz_cust_site_uses_all bill_hcsu

,fusion.hz_cust_acct_sites_all bill_hcas

,fusion.hz_party_sites bill_hps

,fusion.hz_locations bill_hl

,fusion.doo_order_addresses_v ship_add

,fusion.hz_cust_accounts ship_hca

,fusion.hz_parties ship_hp

-- ,fusion.hz_cust_site_uses_all ship_hcsu

-- ,fusion.hz_cust_acct_sites_all ship_hcas

,fusion.hz_party_sites ship_hps

,fusion.hz_locations ship_hl

where 1=1

and hou.organization_id = h.org_id

and hp.party_id (+)= h.sold_to_Party_id

and hca.party_id (+) = h.sold_to_Party_id

and bill_add.address_use_type = 'BILL_TO'

AND H.header_id = bill_add.header_id (+)

and bill_add.cust_acct_id = bill_hca.cust_account_id (+)

AND bill_hca.party_id = bill_hp.party_id (+)

AND bill_add.cust_acct_site_use_id = bill_hcsu.site_use_id (+)

AND bill_hcsu.cust_acct_site_id = bill_hcas.cust_acct_site_id (+)

AND bill_hcas.party_site_id = bill_hps.party_site_id (+)

AND bill_hps.location_id = bill_hl.location_id (+)

and ship_add.address_use_type = 'SHIP_TO'

AND H.header_id = ship_add.header_id (+)

and ship_add.cust_acct_id = ship_hca.cust_account_id (+)

AND ship_hca.party_id = ship_hp.party_id (+)

-- AND ship_add.cust_acct_site_use_id = ship_hcsu.site_use_id (+)

-- AND ship_hcsu.cust_acct_site_id = ship_hcas.cust_acct_site_id (+)

-- AND ship_hcas.party_site_id = ship_hps.party_site_id (+)

AND ship_add.party_site_id = ship_hps.party_site_id (+)

AND ship_hps.location_id = ship_hl.location_ID (+)

and h.order_number = '98430'

 



 In Order Lines, search with item number



 Select the Item number and click on ok button
















Item details will show, click on the Add button to add the line to order



 Once the line is added, the line status will be Open



SELECT

 h.org_id

,hou.name BU_NAME

,h.header_id

,h.order_number

,hp.party_name Cust_name

,hca.account_number Cust_Acct

,h.sold_to_contact_id Contact

,h.ordered_date

,h.customer_po_number PO_NUM

,h.order_type_code

,h.status_code Heder_Status

,h.open_flag

,h.canceled_flag order_canc_flag

,h.sold_to_customer_id

,h.transactional_currency_code CURR

,h.source_order_number src_order_num

,h.source_order_system src_order_sys

,h.source_revision_number src_rev_num

,h.orig_sys_document_ref src_order_ref

,bill_hp.party_name Bill_To_Cust

,bill_add.cust_acct_site_use_id Bill_site_use_id

,bill_hl.Address1||' '||bill_hl.Address2||' '||bill_hl.city||' '||bill_hl.county||' '||NVL(bill_hl.STATE,bill_hl.PROVINCE)||' '||bill_hl.Postal_code||' '||bill_hl.COUNTRY  Bill_TO_Addr

,ship_hp.party_name ship_To_Cust

,ship_add.cust_acct_site_use_id ship_site_use_id

,ship_hl.Address1||' '||ship_hl.Address2||' '||ship_hl.city||' '||ship_hl.county||' '||NVL(ship_hl.STATE,ship_hl.PROVINCE)||' '||ship_hl.Postal_code||' '||ship_hl.COUNTRY  ship_TO_Addr

,l.display_line_number

,l.line_id

,l.line_number

,l.source_line_number src_line_num

,l.inventory_item_id

,esi.item_number item_num

,l.inventory_organization_id inv_org_id

,l.ordered_qty

,l.Unit_list_Price

,l.canceled_flag line_canc_flag

,l.canceled_qty

,l.shipped_qty

,l.ordered_uom

,l.Unit_Selling_Price

,l.extended_amount

,l.line_type_code

,l.status_code line_status

,fl.request_ship_date

,fl.schedule_ship_date

,fl.promise_ship_date

,fl.customer_po_line_number

from

 fusion.doo_headers_all_v h

,fusion.hr_operating_units hou

,fusion.hz_parties hp

,fusion.hz_cust_accounts hca

,fusion.doo_order_addresses_v bill_add

,fusion.hz_cust_accounts bill_hca

,fusion.hz_parties bill_hp

,fusion.hz_cust_site_uses_all bill_hcsu

,fusion.hz_cust_acct_sites_all bill_hcas

,fusion.hz_party_sites bill_hps

,fusion.hz_locations bill_hl

,fusion.doo_order_addresses_v ship_add

,fusion.hz_cust_accounts ship_hca

,fusion.hz_parties ship_hp

-- ,fusion.hz_cust_site_uses_all ship_hcsu

-- ,fusion.hz_cust_acct_sites_all ship_hcas

,fusion.hz_party_sites ship_hps

,fusion.hz_locations ship_hl

,fusion.doo_lines_all_v l

,fusion.doo_fulfill_lines_all fl

,fusion.egp_system_items esi

where 1=1

and hou.organization_id = h.org_id

and hp.party_id (+)= h.sold_to_Party_id

and hca.party_id (+) = h.sold_to_Party_id

and bill_add.address_use_type = 'BILL_TO'

AND H.header_id = bill_add.header_id (+)

and bill_add.cust_acct_id = bill_hca.cust_account_id (+)

AND bill_hca.party_id = bill_hp.party_id (+)

AND bill_add.cust_acct_site_use_id = bill_hcsu.site_use_id (+)

AND bill_hcsu.cust_acct_site_id = bill_hcas.cust_acct_site_id (+)

AND bill_hcas.party_site_id = bill_hps.party_site_id (+)

AND bill_hps.location_id = bill_hl.location_id (+)

and ship_add.address_use_type = 'SHIP_TO'

AND H.header_id = ship_add.header_id (+)

and ship_add.cust_acct_id = ship_hca.cust_account_id (+)

AND ship_hca.party_id = ship_hp.party_id (+)

-- AND ship_add.cust_acct_site_use_id = ship_hcsu.site_use_id (+)

-- AND ship_hcsu.cust_acct_site_id = ship_hcas.cust_acct_site_id (+)

-- AND ship_hcas.party_site_id = ship_hps.party_site_id (+)

AND ship_add.party_site_id = ship_hps.party_site_id (+)

AND ship_hps.location_id = ship_hl.location_ID (+)

and h.header_id = l.header_id (+)

AND l.line_id = fl.line_id (+)

and esi.inventory_item_id = l.inventory_item_id

and esi.organization_id = l.inventory_organization_id

and h.order_number = '98430'

 

Please click on the below link for more details:

Cloud O2C – Oracle Cloud Order to Cash (O2C) Cycle Process with Technical Flow
https://drive.google.com/file/d/11ClavaFtmyl3e5QwCAeKKUWJXZqP1ECZ



Join the https://t.me/OracleApps88 group or message me at @apps88 (https://t.me/apps88) or (+91) 9059574321 in Telegram, to get more information on Oracle EBS R12/Cloud (Fusion) applications.


If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect