BusinessObjects PeopleSoft Accounts Payable Rapid Mart Business Guide

Version 11.5

Patents

Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352.

Trademarks

Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners.

Copyright

Copyright © 2005 Business Objects. All rights reserved.

Contents Chapter 1

Introduction

5

What is a Rapid Mart? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Rapid Marts Accelerate Time to Value . . . . . . . . . . . . . . . . . . . . . . . . . 6 BusinessObjects Rapid Mart architecture . . . . . . . . . . . . . . . . . . . . . . . 7 About this document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Chapter 2

Overview

9

What you can do with this Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Supported analyses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Related Rapid Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Sharing components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Rapid Mart schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Where the Rapid Mart fits in Customer Order Management and Accounting Cycles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Chapter 3

Subject Areas

17

Voucher Summary Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Voucher Distribution Line section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Open Liabilities section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

BusinessObjects PeopleSoft Accounts Payable Rapid Mart Business Guide

3

Contents

PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Payments section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Chapter 4

Reports

35

Voucher Summary section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Open Invoice Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Item by Supplier Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Distribution Line section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Payables Distribution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Top 10 Spending Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Open Liabilities section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 On-hold Payment Detail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Aging Category Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Payment section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Top 10 Supplier Payments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Payment Register Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

4

BusinessObjects PeopleSoft Accounts Payable Rapid Mart Business Guide

chapter

Introduction

1

Introduction What is a Rapid Mart?

What is a Rapid Mart? BusinessObjects™ Rapid Marts provide packaged extraction of operational data to accelerate the deployment of business intelligence (BI). They are addon products to BusinessObjects™ Data Integrator that combine domain knowledge with data integration best practices to deliver pre-built data models, transformation logic, and data flows for extracting data from enterprise applications from SAP, PeopleSoft, Oracle, J. D. Edwards, Siebel and Salesforce.com. Rapid Marts deliver pre-packaged business content and data flows in a single data platform optimized for BI and analytic applications. Rapid Marts enable business users to report on and gain insight into their enterprise information. Most include prebuilt Business Objects reports and universes that deliver domain knowledge on operational systems. BI tools and analytic tools can access Rapid Mart data directly through SQL queries or indirectly through a Data Integrator RealTime request-response system. Rapid Marts can be implemented individually, or in any combination, to form a single platform that delivers the infrastructure for your company’s internal and external information needs. They can also serve as a staging area for enterprise analytic applications. Rapid Marts provide IT with an accelerated time to value through rapid BI deployments because you can implement them quickly and easily customize them to meet specific analytic requirement.

Rapid Marts Accelerate Time to Value Rapid Marts are packaged data solutions that you can quickly deploy to address specific areas of business analysis. Available Rapid Marts include:

• • • • • • • • • • 6

BusinessObjects Sales Rapid Mart BusinessObjects Cost Center Rapid Mart BusinessObjects Profitability Rapid Mart BusinessObjects General Ledger Rapid Mart BusinessObjects Accounts Receivable Rapid Mart BusinessObjects Accounts Payable Rapid Mart BusinessObjects Federal Financials Rapid Mart BusinessObjects Fixed Assets Rapid Mart BusinessObjects Purchasing Rapid Mart BusinessObjects Inventory Rapid Mart

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Introduction About this document

• • • • • • •

BusinessObjects Plant Maintenance Rapid Mart BusinessObjects HR Rapid Mart BusinessObjects Production Planning Rapid Mart BusinessObjects Project Systems Rapid Mart BusinessObjects Pipeline Rapid Mart BusinessObjects Call Center Rapid Mart BusinessObjects Campaign Rapid Mart

You can combine multiple Rapid Marts into a single environment to build the foundation for your data warehouse or use them as a staging area for BusinessObjects Analytic Applications.

BusinessObjects Rapid Mart architecture Rapid Marts provide a powerful jumpstart for building source-specific data marts for operational reporting and packaged data integration for enterprise analytic applications. The following diagram shows where Rapid Marts fit in the deployemt of business intelligence process:

About this document This document describes the BusinessObjects PeopleSoft Accounts Payable Rapid Mart. This document contains information for a variety of users— information that helps you understand the use of the Rapid Mart, the data in the Rapid Mart, the queries you can answer with the Rapid Mart, and how to run, update, and maintain the Rapid Mart.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

7

1

1

Introduction About this document

This document contains the following chapters:

8

•

Chapter 2: Overview — Describes the business problems you can solve and the types of analyses you can do with this Rapid Mart

•

Chapter 3: Subject Areas — Contains detailed information about each section that is part of the batch extraction in theRapid Mart, including the processes each section captures

•

Chapter 4: Reports — Provides examples of reports you can produce

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

chapter

Overview

2

Overview Overview

Overview This chapter describes the business problems you can solve with the BusinessObjects PeopleSoft Accounts Payable Rapid Mart and the types of analyses you can do with this Rapid Mart. The information in this chapter is useful for those who want a business-level overview of the Rapid Mart and its benefits. This chapter contains the following sections:

• • • •

What you can do with this Rapid Mart Supported analyses Related Rapid Marts Rapid Mart schema

The BusinessObjects PeopleSoft Accounts Payable Rapid Mart is made up of several sections. Each section supports a subject related to analysis of payables information: Voucher Summary section - Contains summary information, (summarised at transaction header level) for supplier invoices.

•

Voucher Distribution Line Section - Stores information about invoice distribution lines. It includes distribution type, amount, GL date and distribution account.

•

Open Liability section - Contains details of outstanding unpaid vouchers, including vendor details, as of date, payment terms and scheduled payment date.

•

Payments section - Contains information about invoice payments made to suppliers.

Chapter 3: Subject Areas discusses each of these sections in more detail

10

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Overview What you can do with this Rapid Mart

What you can do with this Rapid Mart The BusinessObjects PeopleSoft Accounts Payable Rapid Mart supports analysis of data in the Payables module of PeopleSoft. PeopleSoft Payables enables financial management to better control the enterprise cashflow whilst maintaining supplier relationships in accordance with financial strategy. It allows analysis of problematic suppliers and internal customers as well as assessing the performance of internal accounts payable operatives. Additionally analysis of vendor discounts will aid decision making for early payments. Being part of the PeopleSoft Financial Suite of modules, PeopleSoft Payables can be integrated with the General ledger module. allowing processing of vendor invoices and other payable documents and generating and tracking outgoing payments. The module has three main functions: managing suppliers, managing payable invoices and managing payments. Using BusinessObjects PeopleSoft Accounts Payable Rapid Mart you can:

•

Query payables transactions based on category, supplier, supplier site, supplier type, currency transaction number, General Ledger date, accounting period and accounting chartfields such as account, alternate account, department and operating unit.

•

Calculate and analyze key Accounts Payables measurements such as DPO.

• • • •

View on-hold invoices. Perform ad hoc analysis. View supplier paid invoice history. analyze cash discounts that have been granted.

The Rapid Mart addresses the following sub-modules

• • • •

Voucher Summary Voucher Distribution Line Open Liability Payments

With this Rapid Mart, you can answer the following kinds of questions:

• •

What are the top 10 spending accounts in the company? What is the number of duplicate payments as of today for this year?

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

11

2

2

Overview Supported analyses

Supported analyses The BusinessObjects PeopleSoft Accounts Payable Rapid Mart supports several types of analyses. Business function

Types of analysis

Measures available in the Rapid Mart

Voucher Summary

Vendor Invoice Summary and Detail

• • • • •

Voucher Distribution Line Voucher Distribution Line analysis

Open Liability

Payments

Unpaid Invoice

Payments to Suppliers

• • • • • • • • • • •

12

Top X Suppliers Listing of invoices over ‘$$$’ amount Un-approved invoices by supplier Cash requirements analysis Expense by category time period such as this quarter vs last quarter or same quarter last year Posted invoices listing Analysis of expense by chartfield (Account Alternate Account, Department and Operating Unit) Invoice Item analysis Payment Terms comparison Discounts applied Invoices on-hold analysis Invoices withheld analysis Unpaid invoices by supplier Payment details listing Analysis of purchase patterns to specific suppliers Payment methods, status and cancel actions

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Overview Related Rapid Marts

Related Rapid Marts Each Rapid Mart is composed of multiple components. A component is a stand-alone work flow that completes a particular task, such as loading a specific dimension table. Components can contain other components. A section is a set of components that address a particular business problem or subject area. A section is itself a component. The BusinessObjects PeopleSoft Accounts Payable Rapid Mart shares a few components as well as some transactional data with the General Ledger Rapid Mart.

•

Several dimensions are used by multiple Rapid Marts. for example, business units, departments and currency components are part of Accounts Payable and General Ledger Rapid Marts.

•

Invoice distribution transactions are posted to the general ledger and can be linked to the journal entry fact table of the General Ledger Rapid Mart

Sharing components The same components can be used in multiple Rapid Marts. For example, a component that extracts information about materials bought, produced, and sold is needed for a Rapid Mart that supports sales analysis and also for a Rapid Mart that supports inventory analysis. Work flows that extract star schema “dimensions” are components. You can add a component to any Rapid Mart using a simple import procedure. A Data Integrator job can include multiple instances of a component. For example, each section includes all the required dimension components. Therefore, a job with several sections may include several instances of a particular dimension component. Components are set to execute only once within a job. This “execute once” feature ensures that shared components do not cause duplicate data extraction from PeopleSoft. For more information about the “execute once” feature, see the Data Integrator Designer Guide. Each of the sections listed in “What you can do with this Rapid Mart” on page 11 is considered a component. You can identify a component within a Data Integrator job by a “C_” prefix before its name. For example, the component that contains voucher header information and the associated reporting dimensions is named C_VoucherSummary_Section_PSF.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

13

2

2

Overview Rapid Mart schema

Rapid Mart schema The following diagram shows the tables in the BusinessObjects PeopleSoft Accounts Payable Rapid Mart. The larger boxes contain the fact tables corresponding to componentized sections; the smaller boxes indicate dimensional components that may apply to multiple sections.

14

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Overview Where the Rapid Mart fits in Customer Order Management and Accounting Cycles

Where the Rapid Mart fits in Customer Order Management and Accounting Cycles PeopleSoft Payables generates supplier invoices and enables managing vendor payables. It is a part of the PeopleSoft Financials suite and can be viewed as a bridge between Purchasing and General Ledger modules.

The BusinessObjects PeopleSoft Accounts Payable Rapid Mart addresses analyses of supplier invoices, payable debit/credit memos and invoice payment ativities.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

15

2

2

Overview Where the Rapid Mart fits in Customer Order Management and Accounting Cycles

16

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

chapter

Subject Areas

3

Subject Areas Overview

Overview Each section in the BusinessObjects PeopleSoft Accounts Payable Rapid Mart pertains to a particular subject area. This chapter describes each section and the processes each section captures. The information in this chapter is useful for readers who use the Accounts Receivable Rapid Mart and need to understand the data in the Accounts Receivable Rapid Mart and how it relates to PeopleSoft. This chapter discusses:

• • • •

Voucher Summary Section Voucher Distribution Line section Open Liabilities section Payments section

Voucher Summary Section The voucher summary section extracts accounts payable voucher information from PeopleSoft. This section extracts information about supplier invoices, credit and debit memos. It provides invoice summary information at the invoice header level. It also provides information at invoice line level prior to further breakdown into GL Account categories (distribution lines).

PeopleSoft processing A Payables voucher consists of a voucher header, one or more voucher lines and a payment schedule. The voucher header stores invoice generic information such as document type, voucher number, supplier, payment terms, currency, various dates and status. It also stores invoice total amounts such as invoiced amount, freight costs, tax amounts and discount amounts. An invoice line contains a further breakdown of the invoiced amount by invoice item. This can then be subdivided again into distribution lines that record the GL account, alternate accounts, departments and operating units that will bear the cost of the voucher. Voucher distrribution lines are further discussed in the Voucher Distribution Line section

18

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Voucher Summary Section

PeopleSoft Payables provides a number of types of payable document some of which are listed below

• • • • • • •

Adjustment voucher Batch Edit Voucher Recurring Voucher Prepaid Voucher Regular Voucher Third Party Voucher Registered Voucher

Rapid Mart processing The Voucher Summary section extracts all records from PeopleSoft voucher header and voucher line transaction tables (note this does not include the voucher distribution line transaction table)

Rapid Mart data The following diagram shows tables that the Payment Schedule section extracts.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

19

3

3

Subject Areas Voucher Summary Section

Fact table columns In addition to detailed vendor invoice information, the voucher fact tables include several status or reference columns that you can use to track voucher attributes. All columns suffixed by _KEY contain surrogate key indexes into the associated dimension table. The VOUCHER_FACT voucher header columns include:

•

BUSINESS_UNIT_KEY, BUSINESS_UNIT_GL_KEY, BUSINESS_UNIT_PO_KEY Identifies the business unit, purchasing business unit and GL business unit for which the voucher involves.

•

VOUCHER_ID, PO_ID The voucher internal identifier and purchase order identifier.

•

VOUCHER_STYLE_DESCR The type of voucher created examples of which include journal, regular or adjustment vouchers

•

INVOICE_ID, INVOICE_DT Date and identifier of the invoice requiring payment

•

VENDOR_TYPE_KEY, VENDOR_ADDRESS_KEY, VENDOR_KEY Attributes of the vendor requiring payment.

•

OPRID Contact details of persons handling the account and voucher details.

•

VCHR_TTL_LINES, VCHR_TEMPLATE_ID, VCHR_TMPLTE_DESCR, VCHR_APPRVL_FLG_DESCR, VOUCHER_TYPE_DESCR, VCHR_SRC_DESCR Voucher attributes

•

CLOSE_STATUS_DESCR, ENTRY_STATUS_DESCR, APPR_STATUS_DESCR Indcates whether the voucher is Open or Closed, the entry status such as Complete, Open, Postable, Deleted etc and approval status

•

ACCOUNTING_DT, DUE_DT, DSCNT_DUE_DT, LAST_UPDATE_DT, MANUAL_CLOSE_DT, INV_RECPT_DT, RECEIPT_DT, SHIP_DATE Various date fields extracted from the transaction table including the date recognised for accounting and last update date associated with the record insertion. DUE_DT signifies the date that the voucher becomes due for payment.

20

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Voucher Summary Section

•

POST_STATUS_AP_DESCR, POST_STATUS_WTHD_DESCR, DUP_INVOICE_STATUS, VOUCHER_ID_RELATED Status of the voucher posting in AP and ID and status of any duplicate/ related voucher

•

GROSS_AMT, GROSS_AMT_BSE, MERCHANDISE_AMT, MERCH_AMT_BSE

•

RT_TYPE, RATE_MULT, RATE_DIV, CUR_RT_SOURCE_DESCR

Gross and merchandise amounts in base and transaction currencies Exchange rate type, multiplication and division constants for currency conversion purposes.

•

TXN_CURRENCY_KEY, BASE_CURRENCY_KEY, CURRENCY_RPTG_KEY Identifies the base, transaction and reporting currencies

•

DSCNT_AMT, DSCNT_AMT_BSE, DSCNT_PRORATE_FLG, DSCNT_AMT_FLG_DESCR

•

USETAX_AMT, USETAX_AMT_BSE, SALETX_AMT, SALETX_AMT_BSE, SALETX_PRORATE_FLG, SALETX_CALC_AMT, SALETX_CLC_AMT_BSE

Base and transaction discount amounts and pro-rate discount

Tax and sales tax amounts in base and transaction currencies and prorate sales tax

•

VAT_INV_AMT, VAT_INV_AMT_BSE, VAT_NOINV_AMT, VAT_NOINV_AMT_BSE, VAT_BASIS_AMT, VAT_BASIS_AMT_BSE, VAT_CALC_AMT, VAT_ENTRD_AMT, VAT_RCVRY_AMT, VAT_REBATE_AMT, VAT_REBATE_AMT_BSE, VAT_TRANS_AMT, VAT_TRANS_AMT_BSE

•

FREIGHT_AMT, FREIGHT_AMT_BSE, MISC_AMT, MISC_AMT_BSE

VAT amounts in base and transaction currencies. Freight and miscellaneous amounts in base and transaction currencies.

•

COUNTRY_SHIP_TO, COUNTRY_SHIP_FROM

•

PAYMENT_TERMS_KEY, PAY_TRM_BSE_DT_OPT_DESCR, TERMS_BASIS_DT

Country shipping information.

Payment terms information.

•

PAY_AMT, PAY_AMT_BSE Payment amount in transaction and base currency

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

21

3

3

Subject Areas Voucher Summary Section

•

NUM_OF_DAYS_OPEN, NUM_DAYS_PAST_DUE Calculated columns for number of days a voucher is open and the number of days payment is past due.

•

TIME_KEY This is a smart key reference that can be used to associate the DAY field in TIME_DIMENSION with the transaction date.

•

LOAD_DATE and LOAD_TIME The Rapid Mart populates these fields with the date and time of the start of the data extraction from PeopleSoft. For Oracle, the Rapid Mart stores the date as a string in the format of 'YYYY.MM.DD HH24:MI:SS'. For other target databases, the Rapid Mart stores the date as a string in the 'YYYY.MM.DD' format. Similarly, the Rapid Mart stores the time in an 8character string appropriate for your target database. LOAD_DATE and LOAD_TIME can be helpful when using the fact tables in other applications for further data manipulation.

The VOUCHER_LINE_FACT invoice line columns include:

•

BUSINESS_UNIT_KEY, BUSINESS_UNIT_RECV_KEY, BUSINESS_UNIT_PO_KEY Identifies the business unit, purchasing business unit and receiving business unit for which the voucher involves.

•

VOUCHER_ID, PO_ID The voucher internal identifier and purchase order identifier.

•

VOUCHER_LINE_NUM The voucher line number.

•

TOTAL_DISTRIBS Total number of distributions

•

VENDOR_TYPE_KEY, VENDOR_ADDRESS_KEY, VENDOR_KEY Attributes of the vendor requiring payment.

•

INVOICE_DT, ACCOUNTING_DT, DUE_DT, DSCNT_DUE_DT, LAST_UPDATE_DT, INV_RECT_DT, RECEIPT_DT, SHIP_DATE

•

OPRID

Various date fields extracted from the voucher header table. Contact details of persons handling the account and voucher details.

•

LINE_NBR, SCHED_NBR Line and schedule number

22

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Voucher Summary Section

•

MERCHANDISE_AMT, MERCH_AMT_BSE, STATISTIC_AMOUNT Merchandise amounts in transaction and base currencies and statistic amount

•

DESCR

•

INVOICE_ITEM_KEY

Voucher item description Surrogate key identifier to the item being invoiced

•

QTY_VCHR, UNIT_PRICE

•

UNIT_OF_MEASURE_KEY

Quantity vouchered and unit price of item Surrogate key to the unit of measure

•

FRGHT_PRORATE_FLG, DSCNT_APPL_FLG

•

RECEIVER_ID, RECV_LN_NBR

Freight pro-rate and discount applied indicators Receiver ID and line number

•

AMT_ONLY_FLG

•

WITHHOLDING_KEY

Amount only indicator Surrogate key to withholding information

•

TXN_CURRENCY_KEY, BASE_CURRENCY_KEY, CURRENCY_KEY

•

PAYMENT_TERMS_KEY

Identifies the base, transaction and reporting currencies Payment terms information.

•

TIME_KEY This is a smart key reference that can be used to associate the DAY field in TIME_DIMENSION with the transaction date.

•

LOAD_DATE and LOAD_TIME The Rapid Mart populates these fields with the date and time of the start of the data extraction from PeopleSoft. For Oracle, the Rapid Mart stores the date as a string in the format of 'YYYY.MM.DD HH24:MI:SS'. For other target databases, the Rapid Mart stores the date as a string in the 'YYYY.MM.DD' format. Similarly, the Rapid Mart stores the time in an 8character string appropriate for your target database. LOAD_DATE and LOAD_TIME can be helpful when using the fact tables in other applications for further data manipulation.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

23

3

3

Subject Areas Voucher Distribution Line section

Analysis With the tables in this section, you can analyze transactions and payments along several dimensions:

• • • • • •

By vendor and vendor site By voucher category By entry status By payment terms By voucher source By time vs. previous period, year vs. previous year

Typical queries include:

• • •

What is the cash requirement for this month by business unit? What is the monthly item cost/freight ratio trend by business unit? Who was the vendor with the largest voucher amounts for each business unit in the last quarter?

Voucher Distribution Line section The Distribution Line section provides detailed accounting information for the General Ledger. Accounting distribution details include the chartfields, invoiced item amount and quantity of goods or services received and invoiced.

PeopleSoft processing Invoice lines can be entered manually or automatically. Distrbution lines have their own accounting date which is used to create accounting events and transfer payables information to the General Ledger.

Rapid Mart processing The Voucher Distribution Line section extracts all invoice distribution lines from the PeopleSoft Payables voucher distribution transactional table. It also accesses the voucher header transactional table for such attributes as invoice date, supplier and supplier address and payment terms. In addition the voucher line transactional table provides the invoice item information.

24

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Voucher Distribution Line section

Rapid Mart data The following diagram shows tables that the Voucher Distribution Line section extracts.

Fact table columns The distribution line fact table contains transaction measures and attributes at account, alternate account, department and operating unit level. All columns in this tables suffixed by _KEY contain surrogate key indexes into their associated dimension table The columns in the DISTRIB_LINE_FACT table are described below.

•

BUSINESS_UNIT_KEY, BUSINESS_UNIT_RECV_KEY, BUSINESS_UNIT_PO_KEY, BUSINESS_UNIT_GL_KEY Identifies the business unit, purchasing business unit, GL business unit and receiving business unit to which the voucher is associated.

•

VOUCHER_ID, PO_ID The voucher internal identifier and purchase order identifier.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

25

3

3

Subject Areas Voucher Distribution Line section

•

VOUCHER_LINE_NUM, DISTRIB_LINE_NUM The voucher line and distribution line number.

•

VENDOR_TYPE_KEY, VENDOR_ADDRESS_KEY, VENDOR_KEY Attributes of the vendor requiring payment.

•

INVOICE_DT, ACCOUNTING_DT, DUE_DT, DSCNT_DUE_DT, LAST_UPDATE_DT, INV_RECPT_DT, RECEIPT_DT, SHIP_DATE Various date fields extracted from the voucher header table.

•

ACCOUNT_KEY, ALTACCOUNT_KEY, DEPARTMENT_KEY, OPERATING_UNIT_KEY Account, alternate account, department and operating unit surrogate keys to allow distribution to the General Ledger

•

STATISTICS_CODE, STATISTIC_AMOUNT Statistics code and amount

•

PAYMENT_TERMS_KEY, PAY_TRM_BSE_DT_OPT_DESCR, TERMS_BASIS_DT Payment terms information.

•

TXN_CURRENCY_KEY, BASE_CURRENCY_KEY, CURRENCY_RPTG_KEY, CURRENCY_KEY

•

FOREIGN_AMOUNT, MONETARY_AMOUNT, MERCHANDISE_AMT

Identifies the base, transaction and reporting currencies Foreign, monetary and merchandise amounts in transaction currency

•

DSCNT_AMT_GL, FREIGHT_AMT_GL, MEMO_AMT_GL, MISC_AMT_GL, SALETX_AMT_GL, USETAX_AMT_GL, MERCH_AMT_GL

•

OPEN_ITEM_STATUS_DESCR, DESCR

Transaction amounts specific to the GL Specifies whether the voucher is open or closed. DESCR provides the voucher item description

•

INVOICE_ITEM_KEY Surrogate key identifier to the item being invoiced

•

QTY_VCHR Quantity vouchered

•

COST_TYPE Cost type of item purchased

26

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Voucher Distribution Line section

•

FREIGHT_AMT, SALETX_AMT, USETAX_AMT, VAT_INV_AMT, VAT_NONINV_AMT, DSCNT_AMT Voucher amounts

•

MONETARY_AMT_BSE, MERCH_AMT_BSE, SALETX_AMT_BSE, USETAX_AMT_BSE, FREIGHT_AMT_BSE, DSCNT_AMT_BSE

•

RECEIVER_ID

Voucher amounts in base currency Receiver ID

•

WITHHOLDING_KEY

•

RT_TYPE, RATE_MULT, RATE_DIV

Surrogate key to withholding information Exchange rate type, multiplication and division constants for currency conversion purposes.

•

UNIT_OF_MEASURE_KEY

•

TIME_KEY

Surrogate key to the unit of measure information This is a smart key reference that can be used to associate the DAY field in TIME_DIMENSION with the transaction date.

•

LOAD_DATE and LOAD_TIME The Rapid Mart populates these fields with the date and time of the start of the data extraction from PeopleSoft. For Oracle, the Rapid Mart stores the date as a string in the format of 'YYYY.MM.DD HH24:MI:SS'. For other target databases, the Rapid Mart stores the date as a string in the 'YYYY.MM.DD' format. Similarly, the Rapid Mart stores the time in an 8character string appropriate for your target database. LOAD_DATE and LOAD_TIME can be helpful when using the fact tables in other applications for further data manipulation.

Analysis With the tables in this section, you can analyze payables transactions along several dimensions:

• • •

By vendor and vendor site

• •

By voucher and voucher line categories

By business unit By account, alternate account, department, operating unit and associated hierarchies By time period vs. previous period, year vs. previous year

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

27

3

3

Subject Areas Open Liabilities section

Typical queries include:

• • •

Which business units incurred the most expenses last year? What are the top ten spending accounts in the company? Which items have been purchased most often in the year and at what cost?

Open Liabilities section The Open Liabilites section provides detailed information about unpaid invoices

PeopleSoft processing PeopleSoft Payables stores details of vouchers which are yet to receive payment. PeopleSoft Receivables associates an entry status against each voucher recorded. These statuses include: C - Complete O -Open P - Postable R - Recycle S - Scheduled for payment D - Deleted PeopleSoft Payables also records the date payment is scheduled and whether a voucher is on hold or is being withheld.

Rapid Mart processing The Open Liabilities section extracts all unpaid voucher information from the open liabilities transactional table.

28

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Open Liabilities section

Rapid Mart data The following diagram shows tables that the Open Liabilities section extracts.

Fact table columns The open liability fact table contains records of outstanding payments. The columns in the OPEN_LIAB_FACT table are described below. • BUSINESS_UNIT_KEY, BUSINESS_UNIT_GL_KEY

•

Identifies the business unit and GL business unit to which the voucher is associated. VOUCHER_ID, INVOICE_ID

•

The voucher internal identifier and the invoice identifier. PYMNT_CNT

•

The count of payments involved with a particular voucher VOUCHER_LINE_NUM

•

The voucher line number. VENDOR_KEY

•

Identification of the vendor requiring payment. AS_OF_DATE, INVOICE_DT, ACCOUNTING_DT, SCHEDULED_PAY_DT Various date fields including the invoice date and scheduled payment date.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

29

3

3

Subject Areas Open Liabilities section

•

ENTRY_STATUS_DESCR

•

Entry status description such as Complete, Open, Postable Recycle or Deleted. PYMNT_HOLD, PYMNT_HOLD_WTHD

•

Indicators of whether the payment is on hold or is being withheld UNREC_LIAB_IND

•

Unrecorded liability indicator AGE_LIAB_IND, AP_AGING_KEY, APAGING_AGE_BUCKET

•

Aging information dictating whether the payment has become aged or is within the payment terms allowance. Specifies aging categories and surrogate key to the aging dimension GROSS_AMT_BSE, DSCNT_AMT_BSE

•

Gross and discount amounts in base currency DESCR_LN1

•

Description of why a payment is outstanding PAYMENT_TERMS_KEY

•

Payment terms information. BASE_CURRENCY_KEY, CURRENCY_KEY

•

Identifies the base and reporting currencies CUR_RT_TYPE, CUR_EFFDT, RATE_MULT, RATE_DIV

•

Exchange rate type, currency effective date, multiplication and division constants for currency conversion purposes. TIME_KEY

•

This is a smart key reference that can be used to associate the DAY field in TIME_DIMENSION with the transaction date. LOAD_DATE and LOAD_TIME The Rapid Mart populates these fields with the date and time of the start of the data extraction from PeopleSoft. For Oracle, the Rapid Mart stores the date as a string in the format of 'YYYY.MM.DD HH24:MI:SS'. For other target databases, the Rapid Mart stores the date as a string in the 'YYYY.MM.DD' format. Similarly, the Rapid Mart stores the time in an 8character string appropriate for your target database. LOAD_DATE and LOAD_TIME can be helpful when using the fact tables in other applications for further data manipulation.

30

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Payments section

Analysis With the tables in this section, you can analyze open liabilities along several dimensions:

• • • • •

By vendor By payment status By aging status By holding status By time period vs. previous period, year vs. previous year

Typical queries include:

• • • •

How much cash is being held back or withheld from payment? Which payments are open and becoming aged? Which vendors are owed the most amount? Which payments are required imminently in order to satisfy the scheduled payment date?

Payments section The Payments section provides information about payments issued to suppliers.

PeopleSoft processing PeopleSoft Payables creates one row for each payment issued to a supplier or refund received from a supplier in its PS_PAYMENTS_TBL The application stores the supplier name and bank account name for auditing purposes, in case either one is changed after the payment was created. It also stores address information for all payments Payments are recorded with the relevant payment method such as:

• • • •

Draft Direct Debit Electronic Funds Transfer Letter of Credit

Rapid Mart processing The Payments section extracts all payment information from the PS_PAYMENTS_TBL transactional table.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

31

3

3

Subject Areas Payments section

Rapid Mart data The following diagram shows tables that the Payments section extracts.

Fact table columns The AP payment fact table contains details of invoiced payments. The columns in the AP_PAYMENT_FACT table are described below.

•

BANK_SETID, BANK_CD, BANK_ACCT_KEY

•

PYMNT_ID, PYMNT_ID_REF

Identifies the payment banking details. Payment identifier and reference.

•

VENDOR_KEY, VENDOR_TYPE_KEY

•

EMPLID

Identification of the vendor requiring payment. AP employee responsible for making the payment

•

NAME1, NAME2, COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3, COUNTY, STATE, POSTAL, GEO_CODE Name and address details of recipient of payment. These fields ensure payments can be traced and audited

32

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Subject Areas Payments section

•

OPRID User ID

•

PYMNT_AMT Amount of payment made

•

CURRENCY_PYMNT_KEY Specifies the payment currency.

•

ACCOUNTING_DT, CANCEL_DT, PYMNT_DT, CREATION_DT Accounting date, creation date and payment dates associated with making a payment. The cancel date signifies when a payment was cancelled if appropriate.

•

POST_STATUS_AP_DESCR, RECON_STATUS_DESCR, PYMNT_TYPE_DESCR Posting status, reconciling status and type of payment made

•

BUSINESS_UNIT_GL_KEY

•

TIME_KEY

Specifies the Legal Entity GL business unit This is a smart key reference that can be used to associate the DAY field in TIME_DIMENSION with the transaction date.

•

LOAD_DATE and LOAD_TIME The Rapid Mart populates these fields with the date and time of the start of the data extraction from PeopleSoft. For Oracle, the Rapid Mart stores the date as a string in the format of 'YYYY.MM.DD HH24:MI:SS'. For other target databases, the Rapid Mart stores the date as a string in the 'YYYY.MM.DD' format. Similarly, the Rapid Mart stores the time in an 8character string appropriate for your target database. LOAD_DATE and LOAD_TIME can be helpful when using the fact tables in other applications for further data manipulation.

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

33

3

3

Subject Areas Payments section

Analysis With the tables in this section, you can analyze outgoing payments along several dimensions:

• • • • • •

By vendor By bank account By vendor country and region By payment method By payment category By time period vs. previous period, year vs. previous year

Typical queries include:

• • •

34

How many payments are cancelled and for what reason? Which payments are regular, express or manually created? What payments have been stopped, withheld, cancelled or paid?

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

chapter

Reports

4

Reports Overview

Overview The BusinessObjects PeopleSoft Accounts Payable Rapid Mart comes with a sample universe. You can use the Rapid Mart to produce many kinds of reports. This chapter provides examples of reports built on top of the sample universe. The information in this chapter is useful for those who analyze and produce reports with the Rapid Mart data. This chapter provides a few sample reports and the recommended joins for each section in the Rapid Mart:

• • • •

Voucher Summary section Distribution Line section Open Liabilities section Payment section

Voucher Summary section The voucher summary section captures attributes and measures for payable transactions at the document header level.

Sample reports Using the voucher summary information, you can complete numerous analyses and generate different reports. For example, reports you can generate include:

• •

36

Open Invoice Details Item by Supplier Details

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Voucher Summary section

Open Invoice Details This report displays statistics about invoices created for customers. Data is organized by customer, invoice, description and invoice amount.

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT BUSINESS_UNIT.DESCR, CURRENCY.CURRENCY, VENDOR.NAME1, VOUCHER_FACT.ACCOUNTING_DT, VOUCHER_FACT.DUE_DT, VOUCHER_FACT.BASE_CURRENCY_KEY, VOUCHER_FACT.APPR_STATUS_DESCR, VOUCHER_FACT.CLOSE_STATUS_DESCR, sum(VOUCHER_FACT.GROSS_AMT),

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

37

4

4

Reports Voucher Summary section

sum(VOUCHER_FACT.MERCHANDISE_AMT), sum(VOUCHER_FACT.FREIGHT_AMT), AP_PAYMENT_TERMS.DESCR, VOUCHER_FACT.VOUCHER_ID, VOUCHER_FACT.INVOICE_DT FROM BUSINESS_UNIT, CURRENCY, VENDOR, VOUCHER_FACT, AP_PAYMENT_TERMS, DISTRIB_LINE_FACT, VOUCHER_LINE_FACT WHERE ( DISTRIB_LINE_FACT.BASE_CURRENCY_KEY=CURRENCY.CURRENCY_KEY ) AND ( DISTRIB_LINE_FACT.BUSINESS_UNIT_GL_KEY=BUSINESS_UNIT.BUSINESS_UNIT_KEY ) AND ( DISTRIB_LINE_FACT.PAYMENT_TERMS_KEY=AP_PAYMENT_TERMS.AP_PAYMENT_TERMS_KE Y ) AND ( DISTRIB_LINE_FACT.VENDOR_KEY=VENDOR.VENDOR_KEY ) AND ( VOUCHER_FACT.BUSINESS_UNIT_KEY=VOUCHER_LINE_FACT.BUSINESS_UNIT_KEY and VOUCHER_FACT.VOUCHER_ID=VOUCHER_LINE_FACT.VOUCHER_ID ) AND ( DISTRIB_LINE_FACT.BUSINESS_UNIT_KEY=VOUCHER_LINE_FACT.BUSINESS_UNIT_KEY and DISTRIB_LINE_FACT.VOUCHER_ID=VOUCHER_LINE_FACT.VOUCHER_ID and DISTRIB_LINE_FACT.VOUCHER_LINE_NUM=VOUCHER_LINE_FACT.VOUCHER_LINE_NUM ) AND ( VOUCHER_FACT.CLOSE_STATUS_DESCR = 'Open' ) GROUP BY BUSINESS_UNIT.DESCR, CURRENCY.CURRENCY, VENDOR.NAME1, VOUCHER_FACT.ACCOUNTING_DT, VOUCHER_FACT.DUE_DT, VOUCHER_FACT.BASE_CURRENCY_KEY, VOUCHER_FACT.APPR_STATUS_DESCR, VOUCHER_FACT.CLOSE_STATUS_DESCR, AP_PAYMENT_TERMS.DESCR, VOUCHER_FACT.VOUCHER_ID, VOUCHER_FACT.INVOICE_DT HAVING ( sum(VOUCHER_FACT.GROSS_AMT) > 1000000 )

38

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Voucher Summary section

Item by Supplier Details This report displays details of invoice items purchased for particular suppliers. .

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT BUSINESS_UNIT.DESCR, VOUCHER_LINE_FACT.VOUCHER_ID, VENDOR.NAME1, INVOICE_ITEM.DESCR254, VOUCHER_LINE_FACT.INVOICE_DT, VOUCHER_LINE_FACT.DUE_DT, VOUCHER_LINE_FACT.LINE_NBR, sum(VOUCHER_LINE_FACT.QTY_VCHR), sum(VOUCHER_LINE_FACT.UNIT_PRICE), sum(VOUCHER_LINE_FACT.MERCHANDISE_AMT), VOUCHER_LINE_FACT.PO_ID, VOUCHER_LINE_FACT.UNIT_OF_MEASURE_KEY

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

39

4

4

Reports Voucher Summary section

FROM BUSINESS_UNIT, VOUCHER_LINE_FACT, VENDOR, INVOICE_ITEM, DISTRIB_LINE_FACT WHERE ( DISTRIB_LINE_FACT.BUSINESS_UNIT_GL_KEY=BUSINESS_UNIT.BUSINESS_UNIT_KEY ) AND ( INVOICE_ITEM.INVOICE_ITEM_KEY=DISTRIB_LINE_FACT.INVOICE_ITEM_KEY ) AND ( DISTRIB_LINE_FACT.VENDOR_KEY=VENDOR.VENDOR_KEY ) AND ( DISTRIB_LINE_FACT.BUSINESS_UNIT_KEY=VOUCHER_LINE_FACT.BUSINESS_UNIT_KEY and DISTRIB_LINE_FACT.VOUCHER_ID=VOUCHER_LINE_FACT.VOUCHER_ID and DISTRIB_LINE_FACT.VOUCHER_LINE_NUM=VOUCHER_LINE_FACT.VOUCHER_LINE_NUM ) AND ( VOUCHER_LINE_FACT.BUSINESS_UNIT_KEY > -1 AND VOUCHER_LINE_FACT.INVOICE_ITEM_KEY > -1 ) GROUP BY BUSINESS_UNIT.DESCR, VOUCHER_LINE_FACT.VOUCHER_ID, VENDOR.NAME1, INVOICE_ITEM.DESCR254, VOUCHER_LINE_FACT.INVOICE_DT, VOUCHER_LINE_FACT.DUE_DT, VOUCHER_LINE_FACT.LINE_NBR, VOUCHER_LINE_FACT.PO_ID, VOUCHER_LINE_FACT.UNIT_OF_MEASURE_KEY

40

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Voucher Summary section

Recommended table joins When analyzing data from the voucher summary section, you need to create joins between fact and dimension tables. Specifically, you need to create joins between the VOUCHER_FACT and the VOUCHER_LINE_FACT tables with other dimension tables in the Rapid Mart. Fact table

Column name

Dimension table

VOUCHER_FACT

BUSINESS_UNIT_KEY BUSINESS_UNIT

BUSINESS_UNIT_KEY

VOUCHER_FACT

BUSINESS_UNIT_PO_ BUSINESS_UNIT KEY

BUSINESS_UNIT_KEY

VOUCHER_FACT

BUSINESS_UNIT_GL_ BUSINESS_UNIT KEY

BUSINESS_UNIT_KEY

VOUCHER_FACT

VENDOR_ADDRESS_ KEY

VENDOR_ADDRES VENDOR_ADDRESS_ S KEY

VOUCHER_FACT

VENDOR_TYPE_KEY

VENDOR_TYPE

VENDOR_TYPE_KEY

VOUCHER_FACT

VENDOR_KEY

VENDOR

VENDOR_KEY

VOUCHER_FACT

TXN_CURRENCY_KE Y

CURRENCY

CURRENCY_KEY

VOUCHER_FACT

BASE_CURRENCY_K EY

CURRENCY

CURRENCY_KEY

VOUCHER_FACT

CURRENCY_RPTG_K EY

CURRENCY

CURRENCY_KEY

VOUCHER_FACT

PAYMENT_TERMS_KE AP_PAYMENT_TER AP_PAYMENT_TERMS Y MS _KEY

VOUCHER_FACT

TIME_KEY

VOUCHER_LINE_ FACT

BUSINESS_UNIT_KEY BUSINESS_UNIT

BUSINESS_UNIT_KEY

VOUCHER_LINE_ FACT

BUSINESS_UNIT_REC BUSINESS_UNIT V_KEY

BUSINESS_UNIT_KEY

VOUCHER_LINE_ FACT

BUSINESS_UNIT_PO_ BUSINESS_UNIT KEY

BUSINESS_UNIT_KEY

VOUCHER_LINE_ FACT

VENDOR_ADDRESS_ KEY

VENDOR_ADDRES VENDOR_ADDRESS_ S KEY

VOUCHER_LINE_ FACT

VENDOR_TYPE_KEY

VENDOR_TYPE

TIME_DIMENSION

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Column name

TIME_KEY

VENDOR_TYPE_KEY

41

4

4

Reports Distribution Line section

Fact table

Column name

Dimension table

Column name

VOUCHER_LINE_ FACT

VENDOR_KEY

VENDOR

VENDOR_KEY

VOUCHER_LINE_ FACT

INVOICE_ITEM_KEY

INVOICE_ITEM

INVOICE_ITEM_KEY

VOUCHER_LINE_ FACT

UNIT_OF_MEASURE_ UNIT_OF_MEASUR UNIT_OF_MEASURE_ KEY E KEY

VOUCHER_LINE_ FACT

WITHHOLDING_KEY

WITHHOLDING

WITHHOLDING_KEY

VOUCHER_LINE_ FACT

BASE_CURRENCY_K EY

CURRENCY

CURRENCY_KEY

VOUCHER_LINE_ FACT

TXN_CURRENCY_KE Y

CURRENCY

CURRENCY_KEY

VOUCHER_LINE_ FACT

CURRENCY_KEY

CURRENCY

CURRENCY_KEY

VOUCHER_LINE_ FACT

PAYMENT_TERMS_KE AP_PAYMENT_TER AP_PAYMENT_TERMS Y MS _KEY

VOUCHER_LINE_ FACT

TIME_KEY

TIME_DIMENSION

TIME_KEY

Distribution Line section The distribution line fact tables stores payables voucher accounting distribution information.

Sample reports Using the distribution line information, you can complete numerous operational and financial analyses and generate different reports. For example, reports you can generate include:

• •

42

Payables Distribution Top 10 Spending Accounts

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Distribution Line section

Payables Distribution This report displays the voucher payment amount broken down by account and department for a particular business unit

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

43

4

4

Reports Distribution Line section

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT BUSINESS_UNIT.BUSINESS_UNIT, BUSINESS_UNIT.DESCR, DEPARTMENT.DESCR, DEPARTMENT.DEPTID, ACCOUNT.ACCOUNT, ACCOUNT.DESCR, VENDOR.NAME1, VENDOR.VENDOR_ID, DISTRIB_LINE_FACT.VOUCHER_ID, DISTRIB_LINE_FACT.VOUCHER_LINE_NUM, DISTRIB_LINE_FACT.DESCR, sum(DISTRIB_LINE_FACT.MONETARY_AMOUNT) FROM BUSINESS_UNIT, DEPARTMENT, ACCOUNT, VENDOR, DISTRIB_LINE_FACT WHERE ( ACCOUNT.ACCOUNT_KEY=DISTRIB_LINE_FACT.ACCOUNT_KEY ) AND ( DISTRIB_LINE_FACT.BUSINESS_UNIT_GL_KEY=BUSINESS_UNIT.BUSINESS_UNIT_KEY ) AND ( DISTRIB_LINE_FACT.VENDOR_KEY=VENDOR.VENDOR_KEY ) AND ( DEPARTMENT.DEPARTMENT_KEY=DISTRIB_LINE_FACT.DEPARTMENT_KEY ) AND ( DISTRIB_LINE_FACT.DEPARTMENT_KEY > -1 AND DISTRIB_LINE_FACT.ACCOUNT_KEY > -1 AND BUSINESS_UNIT.BUSINESS_UNIT = 'USA01' ) GROUP BY BUSINESS_UNIT.BUSINESS_UNIT, BUSINESS_UNIT.DESCR, DEPARTMENT.DESCR, DEPARTMENT.DEPTID, ACCOUNT.ACCOUNT, ACCOUNT.DESCR, VENDOR.NAME1, VENDOR.VENDOR_ID, DISTRIB_LINE_FACT.VOUCHER_ID, DISTRIB_LINE_FACT.VOUCHER_LINE_NUM, DISTRIB_LINE_FACT.DESCR

44

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Distribution Line section

Top 10 Spending Accounts This report displays the top 10 spending account lines over the year

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT ACCOUNT.ACCOUNT_TYPE, ACCOUNT.ACCOUNT, ACCOUNT.DESCR, CURRENCY.CURRENCY, sum(DISTRIB_LINE_FACT.MERCHANDISE_AMT), DISTRIB_LINE_FACT.TXN_CURRENCY_KEY FROM ACCOUNT, CURRENCY, DISTRIB_LINE_FACT, TIME_DIMENSION, BUSINESS_UNIT WHERE ( ACCOUNT.ACCOUNT_KEY=DISTRIB_LINE_FACT.ACCOUNT_KEY ) AND ( DISTRIB_LINE_FACT.BASE_CURRENCY_KEY=CURRENCY.CURRENCY_KEY ) AND ( DISTRIB_LINE_FACT.BUSINESS_UNIT_GL_KEY=BUSINESS_UNIT.BUSINESS_UNIT_KEY ) AND ( DISTRIB_LINE_FACT.TIME_KEY=TIME_DIMENSION.TIME_KEY )

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

45

4

4

Reports Distribution Line section

AND ( TIME_DIMENSION.YEAR = 2004 AND BUSINESS_UNIT.BUSINESS_UNIT = 'USA01' ) GROUP BY ACCOUNT.ACCOUNT_TYPE, ACCOUNT.ACCOUNT, ACCOUNT.DESCR, CURRENCY.CURRENCY, DISTRIB_LINE_FACT.TXN_CURRENCY_KEY

Recommended table joins When analyzing data from the payment schedule section, you need to create joins between fact and dimension tables. Specifically, you need to create joins between the DISTIRB_LINE_FACT table with other dimension tables in the Rapid Mart. Fact table

Column name

Dimension table

Column name

DISTRIB_LINE_FA BUSINESS_UNIT_KE CT Y

BUSINESS_UNIT

BUSINESS_UNIT_KEY

DISTRIB_LINE_FA BUSINESS_UNIT_RE CT CV_KEY

BUSINESS_UNIT

BUSINESS_UNIT_KEY

DISTRIB_LINE_FA BUSINESS_UNIT_PO CT _KEY

BUSINESS_UNIT

BUSINESS_UNIT_KEY

DISTRIB_LINE_FA BUSINESS_UNIT_GL_ BUSINESS_UNIT CT KEY

BUSINESS_UNIT_KEY

DISTRIB_LINE_FA VENDOR_ADDRESS_ VENDOR_ADDRES VENDOR_ADDRESS_K CT KEY S EY DISTRIB_LINE_FA VENDOR_TYPE_KEY CT

VENDOR_TYPE

VENDOR_TYPE_KEY

DISTRIB_LINE_FA VENDOR_KEY CT

VENDOR

VENDOR_KEY

DISTRIB_LINE_FA ACCOUNT_KEY CT

ACCOUNT

ACCOUNT_KEY

DISTRIB_LINE_FA ALTACCOUNT_KEY CT

ALTACCOUNT

ALTACCOUNT_KEY

DISTRIB_LINE_FA DEPARTMENT_KEY CT

DEPARTMENT

DEPARTMENT_KEY

46

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Distribution Line section

Fact table

Column name

Dimension table

Column name

DISTRIB_LINE_FA OPERATING_UNIT_K CT EY

OPERATING_UNIT OPERATING_UNIT_KE Y

DISTRIB_LINE_FA INVOICE_ITEM_KEY CT

INVOICE_ITEM

INVOICE_ITEM_KEY

DISTRIB_LINE_FA UNIT_OF_MEASURE_ UNIT_OF_MEASU CT KEY RE

UNIT_OF_MEASURE_K EY

DISTRIB_LINE_FA WITHHOLDING_KEY CT

WITHHOLDING_KEY

WITHHOLDING

DISTRIB_LINE_FA BASE_CURRENCY_K CURRENCY CT EY

CURRENCY_KEY

DISTRIB_LINE_FA TXN_CURRENCY_KE CURRENCY CT Y

CURRENCY_KEY

DISTRIB_LINE_FA CURRENCY_KEY CT

CURRENCY

CURRENCY_KEY

DISTRIB_LINE_FA CURRENCY_RPTG_K CURRENCY CT EY

CURRENCY_KEY

DISTRIB_LINE_FA PAYMENT_TERMS_K CT EY

AP_PAYMENT_TE RMS

AP_PAYMENT_TERMS_ KEY

DISTRIB_LINE_FA TIME_KEY CT

TIME_DIMENSION

TIME_KEY

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

47

4

4

Reports Open Liabilities section

Open Liabilities section The open liabilities section fact tables contains details of outstanding, withheld and aging payments.

Sample reports Using the open liabilities information, you can complete numerous variance analyses and generate different reports. For example, reports you can generate include:

• •

On-hold Payment Detail Aging Category Analysis

On-hold Payment Detail This report allows viewing details of payments that have been put on hold in the quarter so that some idea of how much cash may have to be reserved for future payment.

48

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Open Liabilities section

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT OPEN_LIAB_FACT.AS_OF_DATE, OPEN_LIAB_FACT.BASE_CURRENCY_KEY, CURRENCY.CURRENCY, sum(OPEN_LIAB_FACT.GROSS_AMT_BSE), sum(OPEN_LIAB_FACT.DSCNT_AMT_BSE), OPEN_LIAB_FACT.INVOICE_DT, OPEN_LIAB_FACT.INVOICE_ID, OPEN_LIAB_FACT.PYMNT_CNT, OPEN_LIAB_FACT.VOUCHER_ID, VENDOR.NAME1, AP_PAYMENT_TERMS.DESCR FROM OPEN_LIAB_FACT, CURRENCY, VENDOR, AP_PAYMENT_TERMS, BUSINESS_UNIT, TIME_DIMENSION WHERE ( CURRENCY.CURRENCY_KEY=OPEN_LIAB_FACT.BASE_CURRENCY_KEY ) AND ( BUSINESS_UNIT.BUSINESS_UNIT_KEY=OPEN_LIAB_FACT.BUSINESS_UNIT_GL_KEY ) AND ( AP_PAYMENT_TERMS.AP_PAYMENT_TERMS_KEY=OPEN_LIAB_FACT.PAYMENT_TERMS_KEY ) AND ( TIME_DIMENSION.TIME_KEY=OPEN_LIAB_FACT.TIME_KEY ) AND ( VENDOR.VENDOR_KEY=OPEN_LIAB_FACT.VENDOR_KEY ) AND ( OPEN_LIAB_FACT.PYMNT_HOLD = 'Y' AND BUSINESS_UNIT.BUSINESS_UNIT = 'USA01' AND TIME_DIMENSION.YEAR = 2004 AND TIME_DIMENSION.QUARTER = 1 ) GROUP BY OPEN_LIAB_FACT.AS_OF_DATE, OPEN_LIAB_FACT.BASE_CURRENCY_KEY, CURRENCY.CURRENCY, OPEN_LIAB_FACT.INVOICE_DT, OPEN_LIAB_FACT.INVOICE_ID, OPEN_LIAB_FACT.PYMNT_CNT, OPEN_LIAB_FACT.VOUCHER_ID, VENDOR.NAME1, AP_PAYMENT_TERMS.DESCR

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

49

4

4

Reports Open Liabilities section

Aging Category Analysis This report analyzes payments that have aged beyond the payment terms period. These payments may be liable to a surcharge being imposed by the supplier. The report uses standard categories of 0-30 days (reserved for payments due for immediate payment), 31-60 days, 61-90 days and greater than 90 days. The initial category has been ignored to concentrate on those payments in the later payment categories.

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT VENDOR.NAME1, OPEN_LIAB_FACT.INVOICE_ID, CURRENCY.CURRENCY, sum(case when OPEN_LIAB_FACT.AP_AGING_KEY=4 then OPEN_LIAB_FACT.GROSS_AMT_BSE end), sum(case when OPEN_LIAB_FACT.AP_AGING_KEY=5 then OPEN_LIAB_FACT.GROSS_AMT_BSE end), sum(case when OPEN_LIAB_FACT.AP_AGING_KEY=6 then OPEN_LIAB_FACT.GROSS_AMT_BSE end), sum(case when OPEN_LIAB_FACT.AP_AGING_KEY=7 then OPEN_LIAB_FACT.GROSS_AMT_BSE end), OPEN_LIAB_FACT.INVOICE_DT FROM VENDOR, OPEN_LIAB_FACT, CURRENCY, BUSINESS_UNIT WHERE ( CURRENCY.CURRENCY_KEY=OPEN_LIAB_FACT.BASE_CURRENCY_KEY ) AND ( BUSINESS_UNIT.BUSINESS_UNIT_KEY=OPEN_LIAB_FACT.BUSINESS_UNIT_GL_KEY ) AND ( VENDOR.VENDOR_KEY=OPEN_LIAB_FACT.VENDOR_KEY )

50

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Open Liabilities section

AND ( OPEN_LIAB_FACT.AP_AGING_KEY > 4 AND BUSINESS_UNIT.BUSINESS_UNIT = 'USA01' ) GROUP BY VENDOR.NAME1, OPEN_LIAB_FACT.INVOICE_ID, CURRENCY.CURRENCY, OPEN_LIAB_FACT.INVOICE_DT

Recommended table joins When analyzing data from the cash receipt section, you need to create joins between fact and dimension tables. Specifically, you need to create joins between the OPEN_LIAB_FACT table with other dimension tables in the Rapid Mart. Fact table

Column name

Dimension table

Column name

OPEN_LIAB_FAC T

BUSINESS_UNIT_KEY BUSINESS_UNIT

BUSINESS_UNIT_KEY

OPEN_LIAB_FAC T

BUSINESS_UNIT_GL_ BUSINESS_UNIT KEY

BUSINESS_UNIT_KEY

OPEN_LIAB_FAC T

VENDOR_KEY

VENDOR

VENDOR_KEY

OPEN_LIAB_FAC T

BASE_CURRENCY_K EY

CURRENCY

CURRENCY_KEY

OPEN_LIAB_FAC T

CURRENCY_KEY

CURRENCY

CURRENCY_KEY

OPEN_LIAB_FAC T

PAYMENT_TERMS_K EY

AP_PAYMENT_TE AP_PAYMENT_TERMS_ RMS KEY

OPEN_LIAB_FAC T

TIME_KEY

TIME_DIMENSIO N

TIME_KEY

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

51

4

4

Reports Payment section

Payment section The payment section fact tables contains records of all payments and recipient names and addresses made through the AP system.

Sample reports Using the payment transaction table information, you can complete numerous variance analyses and generate different reports. For example, reports you can generate include:

• •

Top 10 Supplier Payments Payment Register Analysis

Top 10 Supplier Payments This report allows viewing details of payments grouped by vendors that have provided the top 10 accumulated value for their invoices. These can be seen as very important suppliers, it is likely that the business holds a dependency on their products. Finding alternative products at a lower cost could be a wothwhile exercise in order to considerably save expenditure.

52

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Payment section

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT AP_PAYMENT_FACT.ACCOUNTING_DT, AP_PAYMENT_FACT.PYMNT_DT, AP_PAYMENT_FACT.PYMNT_ID, AP_PAYMENT_FACT.PYMNT_TYPE_DESCR, VENDOR.NAME1, sum(AP_PAYMENT_FACT.PYMNT_AMT), CURRENCY.CURRENCY, AP_PAYMENT_FACT.CURRENCY_PYMNT_KEY FROM AP_PAYMENT_FACT, VENDOR, CURRENCY, TIME_DIMENSION, BUSINESS_UNIT WHERE ( BUSINESS_UNIT.BUSINESS_UNIT_KEY=AP_PAYMENT_FACT.BUSINESS_UNIT_GL_KEY ) AND ( CURRENCY.CURRENCY_KEY=AP_PAYMENT_FACT.CURRENCY_PYMNT_KEY ) AND ( TIME_DIMENSION.TIME_KEY=AP_PAYMENT_FACT.TIME_KEY ) AND ( VENDOR.VENDOR_KEY=AP_PAYMENT_FACT.VENDOR_KEY ) AND ( TIME_DIMENSION.MONTH = 4 AND TIME_DIMENSION.YEAR = 2004 AND BUSINESS_UNIT.BUSINESS_UNIT = 'USA01' ) GROUP BY AP_PAYMENT_FACT.ACCOUNTING_DT, AP_PAYMENT_FACT.PYMNT_DT, AP_PAYMENT_FACT.PYMNT_ID, AP_PAYMENT_FACT.PYMNT_TYPE_DESCR, VENDOR.NAME1, CURRENCY.CURRENCY, AP_PAYMENT_FACT.CURRENCY_PYMNT_KEY

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

53

4

4

Reports Payment section

Payment Register Analysis This report analyzes payments made by vendor, payment ID, payment method and payment amount for a particular business unit.

You can generate the data for this report using SQL logic, such as the following Oracle SQL statement: SELECT BUSINESS_UNIT.DESCR, VENDOR.VENDOR_ID, VENDOR.NAME1, AP_PAYMENT_FACT.PYMNT_ID_REF, sum(AP_PAYMENT_FACT.PYMNT_AMT), AP_PAYMENT_FACT.PYMNT_DT, AP_PAYMENT_FACT.PYMNT_METHOD_DESCR, AP_PAYMENT_FACT.PYMNT_STATUS_DESCR FROM BUSINESS_UNIT, VENDOR, AP_PAYMENT_FACT,

54

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Reports Payment section

TIME_DIMENSION WHERE ( BUSINESS_UNIT.BUSINESS_UNIT_KEY=AP_PAYMENT_FACT.BUSINESS_UNIT_GL_KEY ) AND ( TIME_DIMENSION.TIME_KEY=AP_PAYMENT_FACT.TIME_KEY ) AND ( VENDOR.VENDOR_KEY=AP_PAYMENT_FACT.VENDOR_KEY ) AND ( BUSINESS_UNIT.BUSINESS_UNIT = 'USA01' AND AP_PAYMENT_FACT.VENDOR_KEY IN (5, 10, 17, 31, 33, 50) AND TIME_DIMENSION.YEAR = 2004 AND TIME_DIMENSION.MONTH = 04 AND AP_PAYMENT_FACT.PYMNT_STATUS_DESCR = 'Paid' ) GROUP BY BUSINESS_UNIT.DESCR, VENDOR.VENDOR_ID, VENDOR.NAME1, AP_PAYMENT_FACT.PYMNT_ID_REF, AP_PAYMENT_FACT.PYMNT_DT, AP_PAYMENT_FACT.PYMNT_METHOD_DESCR, AP_PAYMENT_FACT.PYMNT_STATUS_DESCR

Recommended table joins When analyzing data from the cash receipt section, you need to create joins between fact and dimension tables. Specifically, you need to create joins between the AP_PAYMENT_FACT table with other dimension tables in the Rapid Mart. Fact table

Column name

Dimension table

AP_PAYMENT_FACT

BUSINESS_UNIT_GL_ BUSINESS_UNIT KEY

BUSINESS_UNIT_KE Y

AP_PAYMENT_FACT

VENDOR_TYPE_KEY

VENDOR_TYPE

VENDOR_TYPE_KEY

AP_PAYMENT_FACT

VENDOR_KEY

VENDOR

VENDOR_KEY

AP_PAYMENT_FACT

CURRENCY_PYMNT_ CURRENCY KEY

CURRENCY_KEY

AP_PAYMENT_FACT

TIME_KEY

TIME_KEY

TIME_DIMENSIO N

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide

Column name

55

4

4

Reports Payment section

56

BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide