Downloading and Creating FRS Report

Role Required:  Financial Analyst

Process:

·       Downloading FRS Studio

·       Log in to FRS Studio

·       Creating Grid and setting up POV(Point of View) Values

·       Formatting the Report output

 

Downloading FRS Studio:

 Navigate to the below path as shown in the screenshot

 Welcome spring board à Navigator Menu à Financial Reporting Center

Allow popup to open FRS Window

Navigate as shown in the below screenshot to Install Studio 

Once the Studio is downloaded, Navigate to the installed location and run as administrator.

 

 Login to FRS Studio:

Open the App and Run as administrator 

 

 

Enter Credentials as below

User Name & Password: User Login Credentials

Server URL: Oracle Instance URL till .com

 

 

Example :

Let us try to build the Report shown in the below screenshot. 

 

 

Creating Grid and setting up POV Values:

From the FRS Studio, follow the Navigation shown in the below screenshot to Create new Report

Create Grid by Dragging and select the Database connection

Now to get the Report in the desired Format, we need to Drag Account to the Rows and Period to the Column as shown and click OK

Once you click on OK , you will see the selected Dimensions in the Grid.

 

To select the desired accounts, double click on the Account Dimension. Member selection screen will appear as shown

First we need to remove the Account present on the Right side by moving to left as shown

Now, expand the account hierarchy and move the desired accounts from left to the right as shown.

Once we select the desired Accounts and move, enable Place selections into separate rows checkbox to see accounts in separate lines.

And click OK.

Now select the Desired Periods by double clicking Accounting Period Dimension in the same way as we selected Accounts.

Now, the grid will look like this

If you run Report at this Stage, you will not see any Data. To get the Data we need to set the POV Members as shown

Double click on any POV Member to set the Value as shown below and click OK

Follow the same for Remaining Point of Views and select Values.

 

Now save the Report in desired Location

 

Now run the Report to see the Values

Output

Formatting Report:

To rename the Accounts to match with the sample 

 Select the account and give the custom heading from Row properties as shown 

Follow for the Remaining rows and columns as required.

Now, we will add the formula row to show the check value as shown

 Give custom heading similar to Accounts

 Enter formula and click on Right mark as shown

 

Now save the Report and Run to view the latest output

Adding borders to the formula row

Right Align Columns and displaying $ symbol

Making column Headings Bold

Now save the Report and Run to view latest output

Adding Report Name in the Header

Now drag the Text Box and add heading and format as shown

 

Position the Header to Display in the Middle as shown

Now save the Report and view the final Output

 

Procure to Pay (P2P) Technical Flow

 P2P cycle flows through the following stages

 

·       Creating Requisition

·       Requisition Approval

·       Creating Purchase Order

·       Purchase Order Approval

·       Creating Invoice and Validating

·       Creation of Payment

·       Transfer and Import Journal to General Ledger

 

Tables Involved at each step:

Requisition:

POR_REQUISITION_HEADERS_ALL

POR_REQUISITION_LINES_ALL

POR_REQ_DISTRIBUTIONS_ALL

 

Approval:

PO_ACTION_HISTORY

 

Purchase Order:

PO_HEADERS_ALL

PO_HEADERS_ARCHIVE_ALL

PO_HEADERS_DRAFT_ALL

 

PO_LINES_ALL

PO_LINES_ARCHIVE_ALL

PO_LINES_DRAFT_ALL

PO_LINE_LOCATIONS_ALL

 

PO_DISTRIBUTIONS_ALL

PO_DISTRIBUTIONS_ARCHIVE_ALL

PO_DISTRIBUTIONS_DRAFT_ALL

 

Invoice:

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

 

AP_INVOICE_PAYMENTS_ALL

AP_INV_APRVL_HIST_ALL

 

Payment:

AP_CHECKS_ALL

AP_HOLDS_ALL

AP_PAYMENT_SCHEDULES_ALL

AP_PAYMENT_HISTORY_ALL

 

General Ledger Transfer:

GL_JE_HEADERS

GL_JE_LINES

GL_IMPORT_REFERENCES

 

XLA_AE_HEADERS

XLA_AE_LINES

XLA_DISTRIBUTION_LINKS

 

Some other useful tables

PO_VERSIONS

POZ_SUPPLIERS

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS_ALL

PO_AGENTS_V

PO_LOOKUP_CODES

AP_LOOKUP_CODES

AP_HISTORY_INVOICES_ALL

AP_HISTORY_CHECKS_ALL

 

P2P Flow chart:

 

 

P2P Query:

 SELECT PRH.REQUISITION_NUMBER,

            PRL.LINE_NUMBER REQ_LINE_NUM,

            PAH.LAST_UPDATED_BY APPROVER,

            PHA.SEGMENT1 PO_NUMBER,

            PLA.LINE_NUM PO_LINE_NUM,

            AIA.INVOICE_NUM,

            AIA.INVOICE_AMOUNT,

            NVL((

                                    SELECT SUM(AMOUNT)

                                    FROM AP_INVOICE_LINES_ALL

                                    WHERE INVOICE_ID = AIA.INVOICE_ID

                                                AND LINE_TYPE_LOOKUP_CODE = 'TAX'

                                    ), 0) TAX,

            ACA.CHECK_NUMBER,

            AIPA.AMOUNT AMOUNT_PAID,

            (

                        SELECT APPROVER_ID

                        FROM (

                                    SELECT AIAH.APPROVER_ID

                                    FROM AP_INV_APRVL_HIST_ALL AIAH

                                    WHERE AIPA.INVOICE_ID = AIAH.INVOICE_ID

                                                AND AIAH.RESPONSE LIKE '%APPROVED'

                                    ORDER BY LAST_UPDATE_DATE

                                    )

                        WHERE ROWNUM = 1

                        ) INVOICE_APPROVER,

            XDL.UNROUNDED_ACCOUNTED_CR,

            XDL.UNROUNDED_ACCOUNTED_DR,

            XAL.AE_LINE_NUM,

            GJH.NAME JOURNAL_NAME,

            GJL.JE_LINE_NUM

FROM POR_REQUISITION_HEADERS_ALL PRH,

            POR_REQUISITION_LINES_ALL PRL,

            PO_ACTION_HISTORY PAH,

            POR_REQ_DISTRIBUTIONS_ALL PRD,

            PO_DISTRIBUTIONS_ALL PDA,

            PO_LINES_ALL PLA,

            PO_HEADERS_ALL PHA,

            AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

            AP_INVOICES_ALL AIA,

            AP_INVOICE_PAYMENTS_ALL AIPA,

            AP_CHECKS_ALL ACA,

            -- AP_INV_APRVL_HIST_ALL AIAH,

            XLA_DISTRIBUTION_LINKS XDL,

            XLA_AE_HEADERS XAH,

            XLA_AE_LINES XAL,

            GL_JE_LINES GJL,

            GL_IMPORT_REFERENCES GIR,

            GL_JE_HEADERS GJH

WHERE 1 = 1

            AND AIA.INVOICE_NUM = :P_INVOICE_NUM

            AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

            AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID

            AND PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID

            AND PAH.SEQUENCE_NUM = (

                        SELECT MAX(SEQUENCE_NUM)

                        FROM PO_ACTION_HISTORY PAH1

                        WHERE PAH1.OBJECT_ID = PAH.OBJECT_ID

                                    AND PAH1.OBJECT_TYPE_CODE = 'REQ'

                                    AND PAH1.ACTION_CODE = 'APPROVE'

                        )

            AND PRD.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID

            AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

            AND PLA.PO_HEADER_ID = PDA.PO_HEADER_ID

            AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID

            AND PDA.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID

            AND AIDA.INVOICE_ID = AIA.INVOICE_ID

            AND AIDA.OBJECT_VERSION_NUMBER = (

                        SELECT MAX(OBJECT_VERSION_NUMBER)

                        FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA1

                        WHERE AIDA1.INVOICE_ID = AIDA.INVOICE_ID

                        )

            AND AIPA.CHECK_ID = ACA.CHECK_ID

            AND AIPA.INVOICE_ID = AIA.INVOICE_ID

            AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID

            AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

            AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

            AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

            AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

            AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

            AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

            AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

            AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID