GL - Oracle Fusion Accounting Scripts

Query to fetch the Sub-Ledger Application and its mapping set in Oracle Cloud Fusion

  SELECT xst.application_name,

         xst.application_id,

         xst.description,

         xmsb.mapping_set_code,

         xmsb.mapping_set_id,

         xmsb.amb_context_code,

         xmsb.enabled_flag ms_enabled_flag,

         xmsb.updated_flag,

         xmsb.last_updated_by,

         xmsb.last_update_date,

         xmsv.mapping_set_value_id,

         xmsv.input_value_constant1,

         xmsv.value_constant,

         xmsv.effective_start_date,

         xmsv.effective_end_date,

         xmsv.enabled_flag

    FROM xla_subledgers_tl    xst,

         xla_mapping_sets_b   xmsb,

         xla_mapping_set_values xmsv

   WHERE     1 = 1

         AND xst.application_id = xmsb.application_id

         AND xmsb.application_id = xmsv.application_id

         AND xmsb.mapping_set_code = xmsv.mapping_set_code

         AND xmsb.amb_context_code = xmsv.amb_context_code

         AND xst.application_name = '<SUB_LEDGER_APPLICATION>'

         AND xst.language = 'US'

ORDER BY xst.application_name, xmsb.mapping_set_code

Query to fetch the GL Accounting Flex Fields Hierarchy Structure. Provided the example for the Company Hierarchy

SELECT tree.tree_structure_code,

       tree.tree_code,

       tree.tree_node_id,

       tree.tree.tree_version_id,

       tree.parent_pk1_value parent,

       tree.pk1_start_value child,

       tree.creation_date,

       tree.created_by,

       tree.last_updated_by,

       tree.last_update_date

  FROM fnd_tree_node tree

 WHERE     tree.tree_structure_code = 'GL_ACCT_FLEX' -- <Provide GL Account Structure Code>

       AND tree.tree_code = 'Company Hierarchy' --<Provide Hierarchy either it can be Company/Account/Dept/Cost_Center>

       AND tree.tree_version_id =

              (SELECT tree_version_id

                 FROM fnd_tree_version_vl

                WHERE tree_code = 'Company Hierarchy'

                      AND SYSDATE BETWEEN effective_start_date AND effective_end_date)

START WITH tree.parent_tree_node_id IS NULL

CONNECT BY PRIOR tree_node_id = parent_tree_node_id

Query to fetch the Accounting Period Status for all applications

SELECT fa.application_short_name,

       fa.application_id,

       fat.application_name,

       gps.set_of_books_id,

       gps.period_name,

       gps.ledger_id,

       gl.name,

       gps.created_by,

       gps.creation_date,

       gps.period_num,

       gps.period_year,

       gps.closing_status,

       DECODE (gps.closing_status,

               'O', 'Open',

               'C', 'Closed',

               'F', 'Future',

               'N', 'Never',

               gps.closing_status)

          gl_status,

       gps.start_date,

       gps.end_date,

       gps.last_updated_by,

       gps.last_update_date

  FROM fnd_application    fa,

       fnd_application_tl fat,

       gl_period_statuses gps,

       gl_ledgers         gl

 WHERE     1 = 1

       AND fat.language = 'US'

       AND fa.application_id = fat.application_id

       AND fa.application_id = gps.application_id

       AND gps.ledger_id = gl.ledger_id

Fusion GL – Oracle Fusion General Ledger Training Manual

01: Oracle Financials Cloud

02: Navigating in General Ledger and Functional Setup Manager

                02 A: Getting Started in Oracle Cloud Applications

                02 B: Exploring the Functional Setup Manager

03: Reviewing Enterprise Structures

                03 A: Reviewing Shared Components

                03 B: Searching for a Legal Entity

                03 C: Entering Values

                03 D: Defining Cross-Validation Rules

                03 E: Creating an Account Combination

                03 F: Creating an Account Shorthand Alias

04: Exploring Calendars and Ledgers

                04 A: Creating a Calendar

                04 B: Creating a Primary Ledger

                04 C: Specifying Ledger Options

                04 D: Defining Reporting Currencies

                04 E: Completing the Ledger Configuration

                04 F: Creating a Ledger Set

                04 G: Reviewing Data Access Set Security

                04 H: Opening the First Accounting Period

05: Creating Journal Entries

                05 A: Creating a Journal Source and Category

                05 B: Entering and Posting a Manual Journal

                05 C: Reverse a Journal Manually

                05 D: Entering and Posting a Journal in a Spreadsheet

                05 E: Using the Account Monitor

06: Using Foreign Currencies

                06 A: Creating a Currency

                06 B: Creating Conversion Rate Types

                06 C: Entering Daily Rates

                06 D: Entering and Reviewing a Foreign Currency Journal

07: Managing Inter-company Transactions

                07 A: Creating Inter-company Transactions with Invoice Generation

                07 B: Running the Reconciliation Report

                07 C: Creating Inter-company Transactions Using a Spreadsheet

08: Performing Period Close

                08 A: Creating and Generating an Allocation Rule

                08 B: Defining and Running a Revaluation

                08 C: Creating a Translation

09: Maintaining General Ledger Options

                09 A: Creating a Suspense Account

                09 B: Creating a Statistical Unit of Measure

                09 C: Creating a Processing Schedule

10: Performing Consolidations

                10 A: Creating a Chart of Accounts Mapping

                10 B: Submitting the Transfer Ledger Balances Process

11: Uploading a Budget Using a Spreadsheet

01: Oracle Financials Cloud

General Ledger Functions and Features

• Information Access: Information is stored in the General Ledger and accessed through online inquiries or reporting and analysis tools such as Smart View, General Accounting infolets, and the Account Monitor. Oracle Fusion General Ledger is the repository of your organization's financial information.

• Financial Controls: Use security features to control access to specific areas and functions of General Ledger.

• Data Collection: Collect data from Oracle Fusion subledgers and non-Oracle feeder systems using journal import, ADF Desktop Integrator, and file-based import.

• Financial Reporting and Analysis: Select from a variety of reporting and analytical tools including the Financial Reporting Center and the BI (Business Intelligence) Catalog.

The General Ledger accounting cycle uses the following steps:

• Open an accounting period.

• Create manual journals, import subledger journals and reverse accrual journals.

• Post your journal batches manually or automatically. The Posting process populates the multidimensional GL balances cube and the relational GL balances table.

• Review the balances created by the posted journals and reconcile the balances to your source subledgers.

• If multicurrency journals exist run the period-end revaluation process. And if subsidiary ledgers in different currencies exist run the translation process.

• If subsidiary ledgers exist, perform consolidation to the parent or corporate ledger.

• Produce financial reports and perform online inquiries to review current account balances.

• Close the current accounting period.

• Open the next accounting period.

Please click on the below link for more details:

Fusion GL – Oracle Fusion General Ledger Training Manual

https://drive.google.com/file/d/1_itsVM6NqMJUzVOSCVuxYqUcUEuQE9td

Please Like and Share our FaceBook page to get more details on Oracle Applications

https://www.facebook.com/pages/Oracle-Applications/712588392100243

Fusion P2P – Oracle Fusion Procure to Pay (P2P) Life Cycle with Functional and Technical flow

The Procure to Pay cycle is vital to an organization as in this process the organization buys and received goods or services from its vendors and makes necessary payments.

The key functionalities for different processes of the Procure-to-Pay cycle are:

In-built intelligence with business transactions--Role based dash boards and work area for buyer, approver; presenting the statistical report, trend, spend analysis, personalized search capability

Enhanced sourcing capabilities--Supplier analysis, visibility of supplier lead time and popularity across departments

Supplier self-service-- Single log-on for supplier providing complete visibility to their transactions and full invoicing capability

Standardized processes -- to facilitate shared services model and centralized procurement function

Smart Receipt capabilities-- Automatic receipt application engine, Over/Under payment rules, scored recommendations based on customer, transaction or exceptions, centralized approach for receipt processing, setup rules to match receipt to the specialist for manual intervention and rules to mark the priority of the receipt based on attributes

Payable dashboard--To collaborate with various participants, visibility of priority items and work volume, item tagging option for quick retrieval

Invoice Work Area-- Invoice creation from scanned images, exception resolution, Invoice creation in a format based on real life physical invoice offering easy data entry and audit

Integrated invoice image processing-- Image attached with the invoice throughout invoice life-cycle

Invoice indicators--Out of balance, status and hold, prepayment alert during entry

Voucher matching -- with the receipt and PO in the same window 

Personalized view-- Excel like capability to freeze columns, personalized search, full screen view, export to excel

One of the best characteristics of the Oracle Fusion applications is that these can be fully integrated with traditional Oracle ERP solutions like PeopleSoft, Oracle EBS. So the existing Oracle customers can choose to implement module wise and selective Fusion functions too. 

                                                                   

With the in-built business intelligence capabilities and business benefits of cloud based applications; Oracle Fusion financials and procurement definitely equip the finance and purchasing users to play a bigger role and add value in overall business achievements. 

The P2P cycle comprises of the following steps,

01. Create Requisitions

02. Approve Requisitions

03. Create Purchase Orders

04. Approve Purchase Orders

05. Receive Goods

06. Create Payables invoice with Match PO Lines

07. Approve Invoice

08. Invoice Payment to Supplier with Electronic Method

09. Approve Payments

10. Verify the Transaction Life Cycle

11. Transfer Payables data to General Ledger

12. Transfer Payments data to General Ledger

13. Verify the Journal Batches

14. Verify the Notifications in Mail

Please click on the below link for more details:

Fusion P2P – Oracle Fusion Procure to Pay (P2P) Life Cycle with Functional and Technical flow

https://drive.google.com/file/d/1vSuqDR14664mG29CuuzQWM3W_ggxr0q0

Please Like and Share our FaceBook page to get more details on Oracle Applications
https://www.facebook.com/pages/Oracle-Applications/712588392100243