AP Supplier Invoices query - FUSION

AP Supplier Invoices query - FUSION

SELECT gp.period_name,
       (SELECT    'Q'
               || quarter_num
               || '-WK'
               || LPAD (TO_CHAR (DECODE (period_num,
                                         53, 14,
                                         DECODE (MOD (period_num, 13),
                                                 0, 13,
                                                 MOD (period_num, 13)
                                                )
                                        )
                                ),
                        2,
                        '0'
                       ) planning_week
          FROM gl_periods gp
         WHERE 1 = 1
           AND dist.accounting_date BETWEEN start_date AND end_date
           AND adjustment_period_flag = 'N'
           AND gp.period_set_name = gl.period_set_name) planning_week,
       (SELECT fv.description
          FROM fnd_flex_values_vl fv,
               fnd_flex_value_sets fvs
         WHERE fv.flex_value_set_id = fvs.flex_value_set_id
           AND fvs.flex_value_set_name = 'Department'
           AND fv.flex_value = gcc.segment3) expenditure_organization,
       inv.project_id project_id,
       NVL ((SELECT p.segment1
               FROM pjf_projects_all_b p
              WHERE p.project_id = inv.project_id), 'N/A') project_number,
       NVL ((SELECT p.NAME
               FROM pjf_projects_all_tl p
              WHERE project_id(+) = inv.project_id), 'N/A') project_name,
       NULL project_type_class_code, hp.party_name employee_vendor,
       DECODE (TO_CHAR (dist.accounting_date, 'DAY'),
               5, dist.accounting_date,
               NEXT_DAY (dist.accounting_date, 6)
              ) week_ending,
       dist.accounting_date expenditure_date,
       inv.invoice_currency_code entered_currency_code,
       dist.amount entered_amount,
       DECODE (inv.invoice_currency_code,
               gl.currency_code, NVL (dist.amount, 0),
               NVL (dist.amount, 0) * NVL (dist.exchange_rate, 0)
              ) accounted_amount,
       dist.exchange_rate accounted_exchange_rate,
       dist.exchange_rate_type accounted_exchange_type,
       (  DECODE (inv.invoice_currency_code,
                  gl.currency_code, NVL (dist.amount, 0),
                  NVL (dist.amount, 0) * NVL (dist.exchange_rate, 1)
                 )
        * NVL ((SELECT gdr.conversion_rate
                  FROM gl_daily_rates gdr
                 WHERE gdr.conversion_type = 'Corporate'
                   AND gdr.to_currency = 'USD'
                   AND gdr.from_currency = gl.currency_code
                   AND gdr.conversion_date = dist.accounting_date),
               1
              )
       ) usd_amount,
       NVL ((SELECT gdr.conversion_rate
               FROM gl_daily_rates gdr
              WHERE gdr.conversion_type = 'Corporate'
                AND gdr.to_currency = 'USD'
                AND gdr.from_currency = gl.currency_code
                AND gdr.conversion_date = dist.accounting_date),
            1
           ) usd_conversion_rate,
       dist.accounting_date conversion_date, gl.NAME set_of_books_name,
       gl.currency_code book_currency_code, gcc.segment1 company,
       gcc.segment2 ACCOUNT, gcc.segment3 department, gcc.segment4 region,
       gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       dist.description comments, inv.invoice_num ap_invoice_number,
       gcc.segment1,
       (SELECT class_code
          FROM pjf_project_classes prc,
               pjf_class_codes_tl pct
         WHERE prc.class_code_id = pct.class_code_id
           AND prc.project_id = inv.project_id) project_region_code
  FROM ap_invoice_distributions_all dist,
       ap_invoice_lines_all line,
       ap_invoices_all inv,
       gl_code_combinations gcc,
       poz_suppliers ps,
       hz_parties hp,
       gl_ledgers gl,
       gl_periods gp
 WHERE dist.invoice_id = line.invoice_id
   AND dist.invoice_line_number = line.line_number
   AND line.invoice_id = inv.invoice_id
   AND dist.dist_code_combination_id = gcc.code_combination_id
   AND inv.vendor_id = ps.vendor_id
   AND hp.party_id = ps.party_id
   AND inv.set_of_books_id = gl.ledger_id
   AND gp.period_set_name = gl.period_set_name
   AND dist.accounting_date BETWEEN gp.start_date AND gp.end_date
   AND gp.adjustment_period_flag = 'N'

Value Sets: Related Tables and Query to Fetch the Details

Value Sets are used in Oracle to define a limited sets of values for a particular field. There are 3 main tables in Oracle Fusion to fetch the values assigned to a value set. Here is a brief description of them:

1. FND_VS_VALUE_SETS: Table to hold the value set information ( Header level data).

2. FND_VS_VALUES_B: Table to hold the values of the value set.

3. FND_VS_VALUES_TL: Table to hold the description of the values.

Now let’s cut to the chase and see the query. Pretty simple it is:

select 

vsval.VALUE_ID,

vsval.VALUE_SET_ID,

vsval.VALUE,

vsval.description,

vt.description,

vsval.flex_value_attribute3,

vsval.CREATION_DATE,

vsval.CREATED_BY,

vsval.LAST_UPDATE_DATE,

vsval.LAST_UPDATED_BY

from  

fnd_vs_value_sets vset,

fnd_vs_values_vl vsval,

FND_VS_VALUES_TL vt

where 

1=1

and vset.value_set_id = vsval.value_set_id

and vsval.CREATION_DATE >= (:pcreationdate) and  vsval.CREATION_DATE <= (:pEnddate) 

and vset.VALUE_SET_CODE = 'Project CCC COA'

AND vt.value_id = vsval.value_id

and vt.language = 'AR'

order by vsval.value

Oracle Fusion Financials

 Oracle Fusion Financials is a complete and integrated suite. It offers all of the core functions in a complete finance system, such as a general ledger to process journals and allocations, accounts payable to process and pay supplier invoices, accounts receivables to invoice customers and receive payments, and so on. It includes a broad suite of applications that are completely integrated. It has your traditional GL, Payables, Receivables, Assets, Expenses, Collections and Cash Management as well as the common module architecture from EBS that includes the centralized Subledger Accounting, centralized customer and supplier data (TCA :Trading Community Architecture), Tax, Payments, and Intercompany.

General Ledger:

This topic includes details about the Oracle Fusion General Ledger reports predefined using Oracle Transactional Business Intelligence (BI).The Oracle Business Intelligence reports for the General Ledger include: Account Analysis Report,General Journals Report & Trial Balance Report

Account Payables:

Oracle Fusion Payables provides predefined reports that cover the following areas:

Invoices

Payments

Payables to Ledger Reconciliation

Period Close

Prepayments

Income Tax and Withholding

Cash Management:

Oracle Fusion Cash Management provides predefined reports that are used in the following areas:

Bank Statements

Cash Transit

Reconciling to the General Ledger

Account Receivables:

Oracle Fusion Receivables provides predefined reports to help you analyze and manage your accounts receivable information.

The Receivables predefined reports cover the following areas:

Bill Presentment Templates

Accounting Reports

Billing Reports

Receivables Balances Reports

Bills Receivable Reports

Reconciliation Program and Report

Fixed Assets:

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions.

The following tables list the predefined reports by type.

Additions by Source Report

Asset Additions Report

Asset Transfers Report

Cost Adjustment Report etc

Tax:

Oracle Fusion Tax provides operational, business, and reconciliation reports for tax activity associated with buying and selling goods and services through Oracle Fusion Payables and Oracle Fusion Receivables. Manual and imported tax transaction details also provide the source for the predefined tax reports.Oracle Fusion Tax reporting also provides reporting data models on which you can prepare custom and specialized tax reports.

Financial Reporting in Oracle Fusion Cloud ERP

Financial Reporting in Oracle Cloud ERP

In this blog I will discuss how financial reports can be created directly from Oracle Cloud ERP. Financial Reports in Oracle Cloud ERP are built off of the Financial Reporting Studio using Metadata and Data from the Oracle Cloud ERP General Ledger Balance Cubes. GL Balances cube stores financial balances in a multidimensional database for interactive reporting and analysis. When the accounting configuration process submitted for a primary or secondary ledger that uses a unique combination of chart of accounts and calendar, a balances cube is created. A ledger determines the currency, chart of accounts, accounting calendar and accounting method. Balances cubes are named after the chart of accounts.

Balances cube stores financial balances in a multidimensional cube for real time, interactive financial reporting, and analysis. Pre-Aggregates balances at every possible point of summarization, ensuring immediate access to financial data and eliminating the need for an external data warehouse for financial reporting. Average balances are tracked in a separate balances cube. Most of the configuration steps while setting up the General Ledger application will cover the creation and population of the GL Balance Cubes.

“Create Cubes” child process which is part of “Create Accounting Configuration” Process creates the accounting configuration and a balances cube when setup is completed. Both Standard and Average Daily Balances cubes if enabled are processed in the same request.

“Publish Chart of Account Dimension Members and Hierarchies to Balances Cubes” process creates the structural hierarchy for the chart of accounts. Both Standard and Average Daily Balances if enabled are processed in the same request.

“Create Ledger Set Dimension Members” child process part of “Create Ledger Set” parent process creates, and updates ledger set members in a balances cube.

Balances cubes are automatically synchronized by the following general ledger processes: open period, posting and translation.

Opening a period lets Journal Entry and Posting allowed.

Posting updates account balances by the recorded debit or credit amount in each journal entry to the related accounts in general ledger.

Translate General Ledger Account Balances process transfers and converts balances from the specified ledger to the reporting currency.

Cubes consists of a set of defining business entities called dimensions. Dimensions in a cube determine how data is accumulated for reporting and analytical purposes. Balances cubes are referred to as an application or database connection in the user interfaces for Financial Reports, Smart View, and Calculation Manager.

There are two types of Oracle Fusion General Ledger Balances cubes: Standard Balances cubes and Average Balances cubes. Balances cubes are named after the chart of accounts they contain.

Standard Balances Cubes

A new standard balances cube is created whenever an accounting configuration is submitted for a ledger, either primary or secondary, that uses a new unique combination of chart of accounts and accounting calendar.

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

Dimensions

• Accounting Period
• Ledger or Ledger Set
• Chart of Accounts Segments (Depending on the Chart of Accounts new dimension for each segment will be created. Example Company, Cost Center, Department & GL Account are created as separate dimensions)
  • Company
  • Cost Center
  • Department
  • GL Account
• Scenario
• Balance Amount
• Amount Type
• Currency
• Currency Type

Average Balances Cubes

Average balances cubes use different dimensions than the standard balances cubes therefore require their results be stored in separate cubes. If the average balances option is enabled for a ledger, a second average balances cube is automatically created based upon the same criteria of a unique combination of chart of accounts and accounting calendar. Average balances cubes are named with ADB (average daily balances) plus the name of the associated chart of accounts.

Dimensions

• Accounting Period
• Ledger or Ledger Set
• Chart of Accounts Segments(Depending on the Chart of Accounts new dimension for each segment will be created. Example Company, Cost Center, Department & GL Account are created as separate dimensions)
  • Company
  • Cost Center
  • Department
  • GL Account
• Scenario
• Amount Type
• Currency
• Currency Type

Financial Reporting Center

Financial Reporting functionality in Oracle Cloud ERP is provided under Financial Reporting Center. Financial Reporting Center is a powerful tool for reviewing, designing, and presenting financial reports and analytic data.

Financial Reports are built off of the Oracle Financial Reporting Studio using data in the General Ledger Balances Cube like Income Statements, Balance Sheets etc.

Configure Workspace Database Connection

Administrators will be able to create database connections from workspace once the General Ledger Balances Cubes are created.

To define a database Connection, Navigate to Financial Reporting Center. From the Financial Reporting Center task panel, Select Open Workspace for Financial Reporting. Select Tools menu and choose Database Connection Manager.

Select New Button.

Enter a user-friendly name for the Database Connection Name.

Enter Essbase as the Type, server name, username, password, and choose the Application (Cube) , Database from the list of values. Expand the Application Name to see the related Database. Click OK to save the selection and close button in the Database Connection Manager window to save the connection.

This database connection will be available in both Workspace and Financial Reporting Studio.

Hyperion Financial Reporting Studio/Web Studio enables to use an object-oriented graphical report layout with report objects such as text boxes, grids, images, and charts.

We will not be discussing how to build the report using the Financial Reporting Studio here. Once the report is developed and saved it will be available under the workspace either under My Folders or under Shared Folders depending the location where the report is saved. End users would be able to run and generated the output in HTML or PDF and is tablet and smartphone friendly.

Financial Reports can be generated by selecting Open or double clicking the Report a pop up will be displayed to choose the parameter values.

Once the parameter values are selected and ok is selected report output will be displayed as below.

Report output can be exported to Excel Smartview in Query Ready format.

e-Invoicing Process in Oracle ERP Cloud View Larger Image e-Invoicing Process in Oracle ERP Cloud

Oracle ERP Cloud provides an integrated Collaboration Messaging Framework to establish business-to-business (B2B) message exchanging capabilities with customers or suppliers. In this blog we will describe how to process an e-Invoice for a customer in Oracle Fusion ERP.

Electronic invoicing (e-Invoicing) is the exchange of the invoice document between a supplier and a buyer and it can be defined as structured invoice data in electronic data interchange (EDI) or XML formats issued, transmitted, received, processed and stored electronically.

Below are the high-level steps that defines the process of generating an AR Invoice in Oracle Fusion ERP.

1. Configuring the customer account for XML Invoicing
2. Create a B2B Trading Partner in Collaboration Messaging
3. Configure the Customer Account for the Trading Partner in Collaboration Messaging
4. Run the process to generate the XML

Describing below the configuration and the process required for each step.

1. Configuring the customer account for XML Invoicing
   Setting the preferred delivery method as XML and select “Enable for XML Invoicing” options.
   Navigation: Customer Account -> Profile History
   • Click on Customer Account
     
   • Navigate to Profile History
     Actions -> Correct Record (If customer account already exist)
     
   • Update Preferred Delivery Method as “XML”
     Select All options in “Enable for XML Invoicing”
     Save and Close
     

2. Create a B2B Trading Partner in Collaboration Messaging
   Navigation: Tools -> Collaboration Messaging
   • Click on Task Lists -> Trading Partners -> Manage B2B Trading Partner
     
   • Enter Service Provider, Trading Partner ID and Partner ID Type.
     Trading Partner ID is same as Registry ID
     Save and Close
     
   • Setup Delivery Methods
     Name: Can be any value
     Delivery Method: UCM File Export
     UCM File Export Document Account : scm/CMKOutbundMessageQueue/export (automatically selected)
     
   • Click on next tab: Outbound Collaboration Messages
     Name: Can be any value
     Collaboration Message Definition: UBL_2.1_INVOICE_OUT (Please note this message definition can be duplicated and customized as per the requirements if needed)
     Delivery Method: Same as created in last step
     Save and Close
     

3. Configure the Customer Account for the Trading Partner in Collaboration Messaging
   Navigation: Tools -> Collaboration Messaging
   Click on Task List -> Trading Partners -> Manage Customer Account Collaboration Configuration
   • Search with customer name and click on Edit Collaboration Configuration
     
   • Service Provider: Selected in previous step (None)
     Trading Partner ID: Select the party id
   • Collaboration Documents for Service Provider
     Document: PROCESS_INVOICE_OUT
     Association Status: Enabled
     

4. Run the process to generate the XML
   • Run the Scheduled Process: Generate and Transfer XML Transactions. This process places the files in UCM as the delivery method selected as UCM File Export.
     
   • How to find the file transmitted to UCM path
     Navigation: Tools -> Fie Import and Export
     
     Search with Account: scm/CMKOutbundMessageQueue/export (this is the standard path used at the time of Delivery Method setup)