BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s 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
9
What is a Rapid Mart? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Rapid Marts Accelerate Time to Value . . . . . . . . . . . . . . . . . . . . . . . . 10 BusinessObjects Rapid Mart architecture . . . . . . . . . . . . . . . . . . . . . . 11 About this document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Chapter 2
Overview
13
What you can do with this Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Supported analyses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Related Rapid Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Sharing components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Rapid Mart schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Where the Rapid Mart fits in Customer Order Management and Accounting Cycles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Chapter 3
Subject Areas
21
Voucher Summary Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Voucher Distribution Line section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Open Liabilities section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
3
Contents
PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Payments section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 PeopleSoft processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Rapid Mart processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Rapid Mart data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Fact table columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Chapter 4
Reports
39
Voucher Summary section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Open Invoice Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Item by Supplier Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Distribution Line section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Payables Distribution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Top 10 Spending Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Open Liabilities section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 On-hold Payment Detail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Aging Category Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Payment section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Sample reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Top 10 Supplier Payments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Payment Register Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Recommended table joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
4
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Contents
Chapter 5
Installing the Rapid Mart
61
System prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Rapid Mart product components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Preparing your environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Installation overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Installation procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Completing the installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Installing Rapid Mart Reporting Content . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Chapter 6
Using the Rapid Mart
71
Recovery framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Data Integrator automatic recovery feature . . . . . . . . . . . . . . . . . . . . . 72 Rapid Mart recovery framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Execution status framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 The execution status table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 How job status is maintained . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 The execution status API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Executing a job with the reset option . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Extraction, transformation and loading process . . . . . . . . . . . . . . . . . . . . . 76 Batch configuration variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Global Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Executing the jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Customizing the Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Chapter 7
Technical Implementation
85
Use of surrogate keys in this Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Loading Vendor and Invoice item dimensions . . . . . . . . . . . . . . . . . . . . . . 89 Voucher Summary section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Dimension table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Fact table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Incremental load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Load dimension tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
5
Contents
Load fact table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Voucher Distribution Line section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Dimension table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Fact table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Incremental load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Load dimension tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Load fact table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Open Liability section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Dimension table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Fact table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Incremental load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Load dimension tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Load fact table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Payment section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Dimension table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Fact table load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Incremental load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Load dimension tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Load fact table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Dealing with NULL values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Fact table stored procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Appendix A
Rapid Mart Data Schema
105
Fact tables in the Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Dimension tables in the Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Other tables in the Rapid Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 ACCOUNT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 ACCOUNT_HIER_HZ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 ACCOUNT_HIER_PC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 ACCOUNT_HIER_VR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 ALTACCOUNT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 ALTACCOUNT_HIER_HZ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 ALTACCOUNT_HIER_PC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
6
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Contents
ALTACCOUNT_HIER_VR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 AP_AGING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 AP_PAYMENT_DISCOUNTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 AP_PAYMENT_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 AP_PAYMENT_TERMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 AW_JOBEXECUTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 BUSINESS_UNIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 COMPANY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 CURRENCY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 DEPARTMENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 DEPARTMENT_HIER_HZ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 DEPARTMENT_HIER_PC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 DEPARTMENT_HIER_VR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128 DISTRIB_LINE_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 FISCAL_CALENDAR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 INVOICE_ITEM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 LOOKUP_INDEXES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 MONTH_DIMENSION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 OPEN_LIAB_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 OPERATING_UNIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 OPERATING_UNIT_HIER_HZ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 OPERATING_UNIT_HIER_PC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 OPERATING_UNIT_HIER_VR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 PS_AP_SET_CNTRL_REC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 STAGE_AP_PAYMENT_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 STAGE_CAL_PERIODS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 STAGE_DISTRIB_LINE_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 STAGE_OPEN_LIAB_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 STAGE_TIME_DIM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 STAGE_VOUCHER_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 STAGE_VOUCHER_LINE_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 TEMP_AP_PAYMENT_DISCOUNTS . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 TEMP_DEPARTMENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
7
Contents
TEMP_VENDOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 TIME_DIMENSION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 UNIT_OF_MEASURE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 VENDOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 VENDOR_ADDRESS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 VENDOR_TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 VOUCHER_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 VOUCHER_LINE_FACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164 WITHHOLDING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
8
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s 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:
• • • • • • • • • • 10
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
11
1
1
Introduction About this document
This document contains the following chapters:
12
•
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
•
Chapter 6: Using the Rapid Mart —Describes how to execute the Rapid Mart, including information about initializing variables and what you need to consider when customizing the Rapid Mart
•
Chapter 7: Technical Implementation —Describes each section and the work flows that run to load that section
•
Appendix A: Overview — Contains a detailed list of the tables and views in the Rapid Mart
Chapter 5: Installing the Rapid Mart — Contains the information you need to install the Rapid Mart
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
14
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
15
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
• • • • • • • • • • •
16
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 15 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
17
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.
18
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
19
2
2
Overview Where the Rapid Mart fits in Customer Order Management and Accounting Cycles
20
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
22
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
23
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.
24
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
25
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
26
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
27
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.
28
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
29
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
30
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
31
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.
32
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
33
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.
34
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
35
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
36
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
37
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:
• • •
38
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:
• •
40
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
41
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 )
42
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
43
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
44
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
45
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:
• •
46
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
47
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
48
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
49
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
50
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
51
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.
52
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
53
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 )
54
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
55
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.
56
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
57
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,
58
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
59
4
4
Reports Payment section
60
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
chapter
Installing the Rapid Mart
5
Installing the Rapid Mart Overview
Overview This chapter contains the information you need to install the BusinessObjects PeopleSoft Accounts Payable Rapid Mart. The information in this chapter is useful for system administrators or others who install the Rapid Mart. This chapter contains the following sections: • System prerequisites • Rapid Mart product components • Preparing your environment • Installation overview • Installation procedure • Completing the installation • Installing Rapid Mart Reporting Content
System prerequisites To run the BusinessObjects PeopleSoft Accounts Payable Rapid Mart, your system requires: • Data Integrator version 11.5.2 or higher compatible version • An Oracle 9i database or Microsoft SQL Server or IBM DB2 UDB 8 database or compatible version. • PeopleSoft release 8.8 or higher compatible version • BusinessObjects Enterprise XIR2 if you want to install Rapid Mart Reporting Content. If you have questions about system prerequisites, contact Business Objects Technical Support.
Rapid Mart product components The Rapid Mart includes several major product components: • A SQL script for defining the Rapid Mart tables • A SQL script for dropping the Rapid Mart tables • An Data Integrator metadata repository that specifies the full load and incremental update jobs for the following: • Data extract from PeopleSoft • Data load into the Rapid Mart • SQL scripts to drop and re-create indexes after loading the Rapid Mart
62
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Installing the Rapid Mart Preparing your environment
• • • •
SQL scripts to create database stored procedures. Dummy procedures are created during installation and procedure samples (for oracle only) are available in Sample\DDL directory. An ERWin diagram of the data schema Set of documents which includes business guide, user guide and release notes An option to install rapid mart reporting contents that includes BusinessObjects Enterprise XIR2 WebIntelligence reports and a universe. See “Installing Rapid Mart Reporting Content” on page 68 for details.
Preparing your environment Follow these steps to prepare your environment for the Rapid Mart installation: 1.
Install Data Integrator, if it is not already installed. Refer to the Data Integrator Getting Started Guide for detailed installation instructions. Make sure that you install the Data Integrator Designer and that you install and configure a Job Server and an Access Server. The default Data Integrator installation creates a repository that you can use for your Rapid Mart. You can also create a separate repository for Rapid Mart after the Data Integrator installation using the Repository Manager. Though you need Data Integrator job server to run Rapid Mart jobs, you need just Data Integrator Designer to install Rapid Mart. This designer can connect to any other job server to process jobs.
2.
Start Data Integrator and log on to the repository where you are going to install the Rapid Mart. This step ensures that your default connection is set appropriately.
3.
Exit Data Integrator.
4.
Prepare your target data warehouse environment. Currently the Rapid Mart supports Oracle, MS SQL Server and DB2 as target databases. You have to supply target database connection informations including username and password if you want to create target schema.
5.
Confirm your source application version. Some of the Rapid Mart does special processing depending upon the source application version. You are now ready to install the Rapid Mart.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
63
5
5
Installing the Rapid Mart Installation overview
Follow these steps to prepare your environment for the Rapid Mart Reporting Content installation: 1.
You need to define an environment variable JAVA_HOME to point to where java.exe exists. Otherwise you will get error message like “Either JAVA_HOME is not defined or the Installer could not find JAVA_HOME. Please define JAVA_HOME upto /bin dir and try again.” For example if you have Business Objects installed in your machine, it generally puts java executable in C:\Program Files\Business Objects\j2sdk1.4.2_08\bin directory. In this case, the JAVA_HOME should be C:\Program Files\Business Objects\j2sdk1.4.2_08
Installation overview The installation program:
• •
Ensures that a compatible version of Data Integrator is installed. Prompts you for information about your system and Rapid Mart environment. The installation program uses this information to customize the Rapid Mart to conform to your environment before loading the Rapid Mart into the repository.
•
Creates the files in the RapidMart\PeopleSoftAP sub-directory of your installation directory, including:
• • • •
SQL files to create and drop tables, indexes and stored procedures. Source atl files to import into Data Integrator Files containing messages and warnings.
Drops and creates the tables in the target database, if requested. This includes the data tables, staging tables, and a Rapid Mart system table, called AW_JOBEXECUTION, which maintains execution status information.
•
Loads and customizes the repository.
After the installation program completes, follow the post installation instructions available in the log file (inslog.txt) found in installation directory.
Installation procedure Follow these steps to install the Rapid Mart:
64
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Installing the Rapid Mart Installation procedure
1.
Put the Rapid Mart CD in your CD drive (on the computer where the Data Integrator Designer is installed). Generally CD pops up the Autorun screen. Otherwise use Windows explorer to go to your CD drive, and then double-click Autorun.exe.
2.
This opens up the autorun screen. Select “Install Rapid Mart” option to install Rapid Mart.
3.
The Welcome window opens. Read its contents to double confirm the name and version of the Rapid Mart that you are installing and then click Next.
4.
The License Agreement window opens. Read its contents and click Yes to indicate that you agree to the product terms and conditions.
5.
The Rapid Mart Destination Folder window opens. Verify that the window lists your preferred installation location. To install in the indicated directory, click Next. To install in a different directory, click Browse and select the desired directory.
6.
The Repository Logon window opens. Fields vary by the type of database that stores the repository. For information about repository logon fields, see the Data Integrator Designer Guide. Enter the information needed to connect and log on to your Data Integrator repository. You can also “Test” the repository connection. This test will make sure that the connection parameters are correct and a valid Data Integrator repository exists in that connection. Once you are satisfied with the connection parameters click Next.
7.
The Rapid Mart Configuration Information window opens.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
65
5
5
Installing the Rapid Mart Installation procedure
Enter or select the configuration for the source datastore, target datastore. Prompt
Description
Source Datastore Name
Select or enter the name of the source datastore from which the Rapid Mart extracts data. For BusinessObjects PeopleSoft Accounts Payable Rapid Mart, the default value is PS_DS. Installer will create this data store even if the datastore does not exist in the repository.
Application Type
Select the type and version of the application server to which you will be connecting. BusinessObjects PeopleSoft Accounts Payable Rapid Mart decides about further processing based on this input.
Target Datastore Name
Select or enter the name of the target datastore into which the Rapid Mart loads data. For BusinessObjects PeopleSoft Accounts Payable Rapid Mart, the default value is RM_DS. Installer will create this data store even if the datastore does not exist in the repository.
Target Database Information Drop and Create DB Objects.
This option will drop (if exists) and create all database objects - tables, indexes and procedures - required for target database schema during the installation process.
Database Type
Select the target database type from one of the available types. Depending upon the selected type other options change.
Database connection Name Appears in the case of oracle database type. Enter the tns connection name to connect to target database. Database Server Name
66
Appears in the case of MS SQL Server database type. Enter the server name on which the SQL server database runs. In case of named instances you may need to add the instance names properly.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Installing the Rapid Mart Installation procedure
Prompt
Description
Database Name
Appears in the case of MS SQL Server database type. Enter the target database name in the SQL Server database.
DB2 Datasource
Appears in the case of DB2 database type. Enter the DB2 data source information.
Username
Database connection Username. Make sure that the user got privileges to create/drop table, indexes and procedures
Password
Database connection Password.
8.
If the source or target datastore that you entered does not currently exist in the repository, the Installation process will create these datastores for you.
9.
If you select the available source and/or target datastores from the drop down list, the installation process will add the repository objects to these data stores but the existing datastore connection parameters would be lost. In other words, the installation process will overwrite the connection parameters if you select existing datastores.
10. You need to check box on Drop and Create DB Objects in case you want to create target database objects (tables, indexes and procedures) to load the data from the source. 11. If you check the Drop and Create DB Object, then you have to enter target database Information. Test the connection parameters and then click Next. 12. Now the “Review” screen appears. You can verify all the parameters - the repository information, the source and target datastore information and the target data warehouse database information. If you want to modify any of the parameters, you can browse Back, or if you are satisfied click Next. 13. The Setup Status window shows the progress of the installation program, displaying messages as the program prepares files, creates tables, and loads the repository. 14. The Final Instructions window lists steps you must consider before running and using the Rapid Mart in your environment. Read these steps, then click Next. 15. The Installation Succeeded window opens. To see the installation log, click the check box and click Finish.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
67
5
5
Installing the Rapid Mart Completing the installation
The installation log (inslog.txt) is stored in the Rapid Mart installation directory that you have selected during the installation process. The file lists the steps that the installation program completed. Scroll to the bottom of the file to see information about the latest install.
Completing the installation To complete the installation, start Data Integrator and log in to the Rapid Mart repository. Before using and running the Rapid Mart in your environment, you must complete several steps:
•
Make sure that you have completed all the steps in the install instructions (found in the inslog.txt in Rapid Mart installation directory).
•
If your Job Server runs on a UNIX system, adjust the directory paths in all Rapid Mart file format definitions to follow the UNIX convention (that is, change “/” to “\”).
•
Adjust the properties of your Application Datastore and target datastore using the Data Integrator Designer:
•
•
using Datastore Configurations, select the appropriate Database Type as Default Configuration
•
adjust the Connection properties, such as database name, user name, password
Set up local and global variables as required using the Data Integrator Designer as described in Chapter 6: Using the Rapid Mart.
The Projects tab in the object library shows the defined projects. The PSFT_AP_Rapid_Mart project contains the job for loading the Rapid Mart—both the initial load and the incremental load. Before running the job that loads the Rapid Mart, you must initialize some variables. For information about executing the Rapid Mart, see Chapter 6: Using the Rapid Mart.
Installing Rapid Mart Reporting Content Rapid Marts come with its own Reporting Content. In order to use these Reporting Content (i.e. supplied WebIntelligence reports and a universe), you must first install them to your BusinessObjects Enterprise XI R2 system and configure a connection to your Rapid Mart target database. If the BusinessObjects Enterprise XI R2 server is located on a different machine, you need to have BusinessObjects Enterprise XI R2 client installed.
68
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Installing the Rapid Mart Installing Rapid Mart Reporting Content
1.
Put the Rapid Mart CD in your CD drive. Generally CD pops up the Autorun screen. Otherwise use Windows explorer to go to your CD drive, and then double-click Autorun.exe.
2.
This opens up the autorun screen. Select “Install Reporting Content” option to install the Reporting Content.
3.
The Welcome window opens. Read its contents to double confirm the name and version of the Rapid Mart Reporting Content that you are installing and then click Next.
4.
Select the directory to use in which the Rapid Mart Reporting Content will be installed. By default this is set to \Business Objects\Rapid Marts\HR. Click Next. Note: The folder specified is used only as temporary storage during installation. Once the installation is complete, this folder can be removed by re-running the setup program, Setup.exe.
5.
Enter a valid login for the BusinessObjects Enterprise XI R2 system to which you wish to publish the Rapid Mart Reporting Content. Click Next. Note: The user name is set to ‘Administrator’ and cannot be changed. The authentication mode is set to ‘secEnterprise’ and cannot be changed.
6.
Click Next at the confirmation screen to start installation.
7.
Now the installer will unpack and release the reporting contents into your BusinessObjects Enterprise system.
8.
Click Finish to complete the installation. Remember, you must configure the universe connection before attempting to run any of the reports.
9.
You can see any installation issues in the installation log (visuals_inslog.txt) found in the installation directory. Note: Please make sure that the BIAR files are installed successfully by confirming the line “[InstallEntSdkWrapper.main] BIAR File Imported successfully“ in the log file. Note: Also if you re-installing the Reporting Content, make sure that you removed the Rapid Mart Reporting Content supplied objects and universes before re-installing. Note: You can also import the BIAR file manually using BusinessObjects Enterprise XI R2 Import Wizard. The BIAR file can be found in the specified installation directory.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
69
5
5
Installing the Rapid Mart Installing Rapid Mart Reporting Content
10. Rapid Mart Reporting Content are published to a folder called ‘Rapid Mart Reporting Content’ in your BusinessObjects Enterprise XI R2 system. In this folder you will find a sub-folder with a name that corresponds to the Rapid Mart you have installed. In this sub-folder you will find the reports.
70
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
chapter
Using the Rapid Mart
6
Using the Rapid Mart Overview
Overview This chapter describes how to execute the Rapid Mart, including information about the variables that the Rapid Mart uses. This chapter also discusses considerations you need to make when customizing the Rapid Mart. The information in this chapter is useful for administrators and others who run or modify the Rapid Mart. Specific topics include:
• • • • •
Recovery framework Execution status framework Extraction, transformation and loading process Batch configuration variables Customizing the Rapid Mart
Recovery framework When executing the Rapid Mart, it is important that you do not load any duplicate data and that you do not extract data redundantly, even in cases of failure. There are two mechanisms to ensure that you do not load duplicate data:
• •
Data Integrator automatic recovery feature Rapid Mart recovery framework
Data Integrator automatic recovery feature With automatic recovery, Data Integrator records the result of each successfully completed step in a job. If a job fails, you can choose to run the job again in recovery mode. During recovery mode, Data Integrator retrieves the results for successfully completed steps and reruns uncompleted or failed steps under the same conditions as the original job. For recovery purposes, Data Integrator considers steps that raise exceptions as failed steps, even if the step is caught in a try/catch block. To use the automatic recovery feature, you must enable the feature during the initial execution of a job. In the Execution Properties window, select the Enable Recovery check box. The automatic recovery option is useful during testing and development phases. However, use caution with this feature during a production run because the data may lose its integrity if significant changes in PeopleSoft source tables occur between the failed run and the recovery run.
72
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Using the Rapid Mart Recovery framework
For example, suppose two tables contain related data but are loaded in separate work flows. In this case, the job can fail after successfully completing the work flow that loads the first table but before completing the work flow that loads the second table. During recovery, Data Integrator does not re-run a successfully completed work flow; therefore, the first table is not loaded again. However, Data Integrator will run the work flow that loads the second table during the recovery job. If the work flow loads the entire table regardless of dates, the second table will contain data added to PeopleSoft between the initial run and the recovery run, but the first table will not contain this data. As a result, the two tables will be inconsistent.
Rapid Mart recovery framework The Rapid Mart contains an external execution status table (AW_JOBEXECUTION) that tracks the status of a job’s execution and the time data was extracted. By using the execution status table, the Rapid Mart ensures that data for the proper dates is extracted from the source datastore. With each execution, the Rapid Mart job extracts data between a start date and an end date. If the job fails and is rerun on the same or a subsequent day, the new job uses the failed job’s start date to ensure that all the data is extracted contiguously. Upon execution, a job first checks the status of the last execution of the job. If the last execution failed, the job determines the ending date of the last successful execution. This date becomes the starting date of the next execution of the job. All work flows are re-executed using the same starting date to ensure consistency. For example, if a failed job is restarted on the same day (without using the automatic recovery feature), the restarted job refreshes all tables because the last execution of the job on that day was not successful. If a job continues to fail over several days, the job would continue using the same start date used on the first failed attempt. This ensures that when the job is eventually successful, it extracts the complete set of data that changed since the start date. To maintain consistency with data flowing to PeopleSoft, the Rapid Mart always overlaps one day’s worth of data in incremental loads. For example, if the end date is June 3, the incremental load job extracts data entered or changed on June 3. The next incremental load job extracts data starting with the June 3 date. Up to one day’s worth of duplicate data may be extracted each time the job runs; however, work flows are designed to detect and delete duplicate data from the target data table before reloading the refreshed data. To do this,
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
73
6
6
Using the Rapid Mart Execution status framework
many work flows use the auto correct load option. The auto correct load option checks for an existing row with the same key as each row flagged as an insert or update activity. If the row exists, this option updates the row. If the row does not exist, this option inserts the row.
Execution status framework The Rapid Mart uses two mechanisms to check the job execution status and control execution:
•
An execution status table that stores one record for each job. That record contains:
• • •
Status of previous job execution Starting and ending times of the last attempted execution of the job
An application programming interface (API) that updates the status table and supports the implementation of restartable jobs and flows
The execution status table The Rapid Mart installation procedure creates a table named AW_JOBEXECUTION in the target database. The execution status table has four columns. Table column
Description
NAME
The name of the job being executed. If the same target datastore is used for more than one repository, you must ensure that all job names are unique.
STATUS
The status of this job after the last execution. Possible status values are: started, done, or none.
EXTRACTLOW
Last attempted start date of data retrieval. This date is also the ending date of the last successful run of the job.
EXTRACTHIGH
Last attempted end date of data retrieval.
How job status is maintained The Rapid Mart records a job status for each invocation of a particular job. There are three possible values for job status:
74
• •
None — The job status is none when the job has never been executed.
•
Done — Job status is set to done when a job completes successfully.
Started — Job status is set to started when a new invocation of a job starts.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Using the Rapid Mart Execution status framework
When a job is started, the Rapid Mart checks the job’s existing status. If the previous execution of the job did not complete successfully—that is, the job status is not done—the Rapid Mart resets the starting date for data extraction ($G_SDATE) from the value specified in the job initialization script to the ending date of the last successful run (EXTRACTHIGH). You can override the reassignment of the starting date by forcing the starting date to be reset. See
“Executing a job with the reset option” on page 76. The Rapid Mart never changes the ending value ($G_EDATE) from the value specified in the initialization script.
After checking the job’s existing status, the Rapid Mart sets the status of the job to started, and starts a new execution of the job. When a job successfully completes, the Rapid Mart sets the job’s status to done. If the job does not complete successfully, the job’s status remains set to started. The EXTRACTLOW and EXTRACTHIGH dates remain set to the current values.
The execution status API The execution status API updates the execution status table to control gaps in the time sequence of data extraction. The API contains two external functions. These functions mark the start and termination of jobs.
•
AW_StartJob ($jobname input, $run_mode input, $load_type input, extractlow input/ output, extracthigh date input/output)
The InitializeJob script calls this function to initialize a job and check the last execution status. When called for a new job execution, the function inserts a new row into table AW_JOBEXECUTION. When called for a restarted job execution, the function:
•
Checks the run mode to see if you set the reset option. If $G_RUN_MODE is RESET, the function resets EXTRACTLOW and EXTRACTHIGH to the $G_SDATE and $G_EDATE values specified in the initialization script.
•
Checks the last execution status of the job. If the job status is done, the function sets EXTRACTLOW to the previous value of EXTRACTHIGH, the last successful end date; next, the function sets EXTRACTHIGH to the value of $G_EDATE,
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
75
6
6
Using the Rapid Mart Extraction, transformation and loading process
which you set in the initialization script. The function returns the values in EXTRACTLOW and EXTRACTHIGH and the Rapid Mart uses these values to update $G_SDATE and $G_EDATE. If the job status is started, the function does not change the value of EXTRACTLOW. Instead, this value remains the end date of the last successful execution. The function does change EXTRACTHIGH to the new value set in $G_EDATE. The function returns the EXTRACTLOW and EXTRACTHIGH values, and the Rapid Mart uses these values to update $G_SDATE and $G_EDATE.
•
AW_EndJob(job name)
The EndJob script calls this function to change the job status value in the AW_JOBEXECUTION table to done when the job executes successfully.
Executing a job with the reset option You can use the reset mode to force the Rapid Mart to assign a new starting extract time for data extracts. Use reset mode to force the starting and ending dates to remain the same as the values set in the initialization script. Note: Using this option may cause gaps in the time sequence of data loaded into target tables. To specify reset mode, edit the job’s initialization script and uncomment the line that sets $G_RUN_MODE to RESET. When using reset mode, be sure that the variable $G_SDATE is set to the appropriate starting date so that no gaps occur.
Extraction, transformation and loading process The job PSFT_AR_Load executes objects that load the BusinessObjects PeopleSoft Accounts Payable Rapid Mart. This job contains:
76
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Using the Rapid Mart Extraction, transformation and loading process
•
Job global variable properties. This window can be accessed by rightclicking on the PSFT_AR_Load job and selecting the Properties command from the drop-down list. Most of the Accounts Receivable Rapid Mart global variables are initialized here.
•
An initialization script. The script contains description of each global variable set in the job Properties window. It also completes several steps:
•
•
Sets the initial values of the Rapid Mart framework variables that the job uses
•
Initiates the execution status framework
Sections that load a set of tables you can use to analyze and report on a particular subject area in the Rapid Mart. A section consists of:
•
A work flow that loads the reporting dimensions applicable to the subject area.
•
Work flows that load holding tables, intermediate tables, and auxiliary tables that support loading the main subject area tables. Some of these flows might be reusable components, which can be incorporated into multiple sections of Rapid Marts. Components have the “execute once” feature enabled.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
77
6
6
Using the Rapid Mart Batch configuration variables
•
•
A conditional object that determines whether the job executes the initial or incremental loading logic.
•
Work flows that load the main tables for the subject area. These tables are generally called “fact” tables in relational modeling terminology. A section loads a single fact table and any additional fact tables that depend on it. For example, an order analysis section might load an order table and an order summary table.
A script that updates the job execution status table
Batch configuration variables The job that load the BusinessObjects PeopleSoft Accounts Payable Rapid Mart uses several variables:
• •
Global Variables Local Variables
Global Variables Global variables are commonly used in other Rapid Marts. You can identify global variables by a “G_” prefix in their names. The InitializeJob script initializes these variables. Variable name
Format
Description
$G_LANGUAGE
char(30)
Language to be Used throughout the Rapid Mart
$G_LOAD_TYPE
char(7)
Controls initial versus incremental loading. Each component includes a conditional that checks the $LOAD_TYPE value. If $LOAD_TYPE is set to FIRST, the initial load logic is executed. If $LOAD_TYPE is DELTA, the incremental load logic is executed. To change from initial load value to incremental load, remove the comment delimiter (#) from statement #$LOAD_TYPE = 'DELTA' and add a comment delimiter to statement $LOAD_TYPE= 'FIRST'
78
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Using the Rapid Mart Batch configuration variables
Variable name
Format
Description
$G_SDATE
YYYY.MM.DD The start date for time-dependent data extraction. During an initial extract, the job does not extract data that was entered into your PeopleSoft system prior to $SDATE. For example, if your start date is January 26, 2000, the initial extract job does not extract data that was entered on January 25, 2000 or earlier. During incremental extractions, the job only extracts data modified or entered on or after this date. The incremental load job only uses the $SDATE when one of these conditions is true: • Execution status table AW_JOBEXECUTION has no rows that correspond to the current job. See “The execution status table” on page 74.
•
The variable $RUN_MODE is set to RESET in the initialization script for the job. See “Executing a job with the reset option” on page 76.
$G_EDATE
YYYY.MM.DD The ending date for time-dependent data extraction. The recommended value for first load is yesterday (sysdate -1). The recommended value for an incremental load is today (sysdate). Jobs do not extract data entered into your PeopleSoft system after this date. For example, if your end date is June 4, 1999, the extract will not extract data for June 5, 1999
$G_LOAD_DATE
datetime
Set to current system date
$G_LOAD_TIME
char(8)
Set to character representation of current system time
$G_DEFAULT_DATE
date
Default value for a date field when it is NULL. The default value is ‘9999.12.31’
$G_DEFAULT_NUMBER
int
Default value for a number field when it is NULL or when it is a foreign key. The default value is -1
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
79
6
6
Using the Rapid Mart Batch configuration variables
Variable name
Format
Description
$G_DEFAULT_TEXT
char(1)
Default value for a character field when it is NULL and when it is not a foreign key. The default value is ‘?’
$G_REBUILD_INDEXES
char(1)
The flag is used by stored procedures called in the pre- and post-processing scripts when loading fact tables. The procedures installed with the Rapid Mart are only placeholders. Recommended procedure definitions are located in ../Rapid Mart/OracleAR/Sample/ DDL/Create_Sample_Maintenance_SP.sql script. If implemented, the procedures will always rebuild indexes after the initial load and only on weekends during incremental loads. See “Fact table stored procedures” on page 104 and the Rapid Mart Deployment Guide for details.
$G_CALENDAR_ID
VARCHAR(2) Name of the Period Set to be used to populate TIME_DIM only. The default value is ‘01’
$G_CALENDAR_SETID
VARCHAR(15 SETID of the Period Set to be used to ) populate TIME_DIM only. Default is ‘SHARE’.
$G_AGING_CYCLEID
VARCHAR(15 Variable to select which aging category set to ) choose in the DF_OpenLiabilitiesFact_Rename_PSF data flow of the Open Liabilities section. The GenerateKeyQuery lookup_ext() function for APAGING_KEY utilizes the $G_AGING_CYCLEID variable in order to make this selection.
Local Variables Local variables are also used in other Rapid Marts. They do not have a specific prefix in their names as global variables have. The InitializeJob script initializes these variables. Variable name
Format
Description
$RUN_MODE
char(7)
When set to RESET and not commented out, this variable forces the Rapid Mart to assign a new starting extract time ($G_SDATE) for data extracts. By default it is commented
80
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Using the Rapid Mart Batch configuration variables
Executing the jobs The steps you complete when executing the PSFT_AR_Load job depend on the type of job:
• •
Initial extraction Incremental extraction
1.
To execute the initial extraction and load In the project area right-click in the PSFT_AR_Load job to access the Properties window.
2.
Edit the job Global Variable properties: a.
Make sure that the value for the $G_LOAD_TYPE variable is set to ‘FIRST’.
b.
Set the starting and ending time periods for the initial load.
c.
Set the starting date (the value for $G_SDATE) low enough to select the earliest data desired for all target tables. Set the ending date (the value for $G_EDATE) to the current date.
d.
Set values for the rest of global variables in the Properties window and click OK to accept the set properties and to close the window.
You can also set global variable values after you select Execute command from the drop-down menu in the project area. For more information about the variables see “Batch configuration variables” on page 78. 3.
Run the job. a.
In the project area, right-click the job and choose Execute.
b.
Select the desired execution properties.
Data Integrator opens the Execution Properties window. At this point, you must decide whether or not to enable automatic recovery. See Data Integrator User Guide for more information. If you enabled automatic recovery and a job failed, you can recover from the failed job at this time. c.
Click OK to accept the selected properties and start the job. The job completes several steps:
•
Initializes the variables to the values you set in the initialization script.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
81
6
6
Using the Rapid Mart Batch configuration variables
•
Calls components to load the tables. Generally, the job truncates the tables before loading them during the initial job. Some components, such as work flows that load dimension tables, are repeated. The job only executes these components the first time because these work flows and data flows are set to “execute only once.”
4.
1.
2.
Check the error log and the execution status table to make sure the job ran successfully. To execute the incremental extraction and load Open the Properties window for job PSFT_AR_Load by right-clicking on the job in the project area and selecting Properties command. Edit the job Global Variable properties: a.
Make sure that the value for the $G_LOAD_TYPE variable is set to ‘DELTA’.
b.
Set the starting and ending time periods for the delta load. The standard setting is the current date for both the starting and ending date.
c.
Set values for the rest of global variables in the Properties window.
d.
Click OK to accept the set properties and to close the window.
Run the job. a.
In the project area, right-click the job and choose Execute. Data Integrator opens the Execution Properties window.
b.
Select the desired execution properties. At this point, you must decide whether or not to enable automatic recovery. See Data Integrator User Guide for more information about automatic recovery. If you enabled automatic recovery and a job failed, you can recover from the failed job at this time.
c.
Click OK to accept the selected properties and start the job. The job completes several steps:
•
82
Initializes the variables to the values you set in the initialization script.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Using the Rapid Mart Customizing the Rapid Mart
•
Calls components to load the tables incrementally, inserting new and changed data since the last job execution. The job deletes outdated data from the target tables, and reloads the target tables with new and changed data. The incremental load uses the append option to load most tables. Some components, such as work flows that load dimension tables, are repeated. The job only executes these components the first time because these work flows and data flows are set to “execute only once.”
3.
Check the error log and the execution status table to make sure the job ran successfully.
Customizing the Rapid Mart Rapid Marts are easy to customize. You can easily:
• •
Customize the Rapid Mart data schema to meet your specific needs.
•
Transport Rapid Mart components and sections to different Rapid Marts using the Data Integrator export and import facilities.
Expand the Rapid Mart to be used with various integrated query, reporting, and data analysis applications.
For example, to customize the Rapid Mart, you might:
•
Change column names to be more meaningful in your business environment.
•
Remove columns or tables you do not need in your business environment.
•
Add:
• • • •
Columns from tables already sourced New columns from tables not extracted Aggregates Calculated or scrubbed data
You change your Rapid Mart in exactly the same way you would change any Data Integrator application. For information about how to make such changes, see the Data Integrator product documentation. Note: In general, whenever you make a change, you need to perform impact analysis. Impacts to consider include:
• •
Initial load Incremental load
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
83
6
6
Using the Rapid Mart Customizing the Rapid Mart
• • • • • •
Target tables Integrity of existing Rapid Mart data Integrity between batch extraction and real time jobs Downstream analysis and reporting and Web applications Variable settings used by imported components and sections Other Rapid Mart (in a multi-Rapid Mart environment)
If you are implementing the Rapid Mart on Oracle 8i or Oracle 9i platforms, you can take advantage of pre-processing and post-processing performance tuning stored procedures described in “Fact table stored procedures” on page 104 and in the Rapid Mart Deployment Guide.
84
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
chapter
Technical Implementation
7
Technical Implementation Overview
Overview This chapter provides technical details about how the BusinessObjects PeopleSoft Accounts Payable Rapid Mart is implemented. This chapter describes each section and the work flows that run to load that section. The information in this chapter is particularly useful for readers who customize the Accounts Payable Rapid Mart for their use. This chapter discusses: • Use of surrogate keys in this Rapid Mart • Loading Vendor and Invoice item dimensions • Voucher Summary section • Voucher Distribution Line section • Open Liability section • Dealing with NULL values • Fact table stored procedures
Use of surrogate keys in this Rapid Mart The Rapid Mart is based upon using surrogate keys for dimension table-fact table joins. Surrogate keys provide processing efficiency when linking dimension tables to fact tables in a star schema. These surrogate keys are created when dimension tables are built and the keys are then identified and inserted into the target fact table when it is created. The PeopleSoftAccounts Payable Rapid Mart has complications linking dimension tables to fact tables due to the usage of the PeopleSoft SETID feature. Unique codes in certain dimensions are only achieved by a combination of the dimension code field and SETID; as it is possible to use the same dimension natural key code a number of times against different SETID’s. SETID is related to Business Unit to enable the same dimension codes to be set up with different attributes depending upon which Business Unit the code is associated with. The fact tables do not require a SETID because the Business Unit identifier exists, with the result that unless the Business Unit is decoded to its related SETID, multiple or incorrect dimension code look ups would result. The PeopleSoft source table PS_SET_CNTRL_REC contains the relationships between each dimension table SETID and Business Unit and this table must be looked up to determine the correct dimension surrogate key to insert in the target fact table. Because the PeopleSoft Accounts Payable Rapid Mart uses surrogate keys, PS_SET_CNTRL_REC is used as a integral part of the surrogate key lookup for relevant dimensions. PS_SET_CNTRL_REC is copied to the target data store to enable efficient referencing, only those records that pertain to the AP dimensions listed are extracted from the source table.
86
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Use of surrogate keys in this Rapid Mart
The SQL required to determine the dimension surrogate key for each of the control table dimensions (listed below for each subject area) has a degree of complexity. The SETID/Business Unit relationship must be derived from looking up Business Unit in the fact table record in the PS_SET_CNTRL_REC table. Owing to this complexity it has been necessary to build the relevant lookup_ext() function using the Custom SQL feature. The custom SQL creates a virtual table of all the SETID/Business Unit combinations possible for the dimension lookup in question. This table is then filtered by the Business Unit for the fact table record within the body of the lookup_ext() function to provide the correct dimension surrogate key. An example of this lookup_ext() custom SQL can be found in the Account dimension lookup as follows: SELECT ACCOUNT.EFFDT ,PS_AP_SET_CNTRL_REC.SETCNTRLVALUE DESCRSHORT ,ACCOUNT.TO_EFFDT ,ACCOUNT.ACCOUNT_KEY ,ACCOUNT.ACCOUNT FROM PS_AP_SET_CNTRL_REC, ACCOUNT WHERE ACCOUNT.SETID = PS_AP_SET_CNTRL_REC.SETID AND PS_AP_SET_CNTRL_REC.RECNAME = \'GL_ACCOUNT_TBL\'
The list of possible Business Units for an account code is returned in the DESCRSHORT column of the SQL and is filtered against Business Unit in the condition section of the lookup_ext() dialog: DESCRSHORT = BUSINESS_UNIT
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
87
7
7
Technical Implementation Use of surrogate keys in this Rapid Mart
The following diagram details the lookup_ext() dialog and Custom Code for the ACCOUNT_KEY lookup
88
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Loading Vendor and Invoice item dimensions
The SETID lookup code is handled in a very similar manner for all affected dimensions as for the Account example. Sometimes other column names are used in the Business Unit filter condition rather than DESCRSHORT as it is a prerequisite of the custom SQL that the translate table chosen must contain the columns specified in the custom SQL. For this reason care must be taken when customising the Rapid Mart to ensure that all the columns referred to in the lookup_ext() custom SQL still exist in each of the dimension tables otherwise errors will occur when running the fact table load. The lookup_ext() custom sql functionality has been used for all SETID related dimensions with the exception of VENDOR, VENDOR_ADDRESS, VENDOR_TYPE and INVOICE_ITEM. These dimensions have their relevant SETID codes stored in the transaction table so do not require the custom SQL decoding.
Loading Vendor and Invoice item dimensions The load procedures for the VENDOR, VENDOR_ADDRESS, VENDOR_TYPE and INVOICE_ITEM dimensions vary from the other dimension loads due to the potentially large number of records that they are likely to handle. These are the only dimensions where the first load and delta load data flows differ as the records must be loaded in the most efficient manner. The Vendor and Item dimensions streamline the first load process by removing the Table_Comparison transform used by the standard dimension first loads. This allows a faster throughput of records than that possible with Table_Comparison. The delta loads still require the Table_Comparison transform in order to ensure that incremental loads of transaction data retain unchanged dimension surrogate keys for prior history. The VENDOR dimension additionally has to perform lookups against VENDOR_ADDRESS and VENDOR_TYPE. As stated above these dimensions can all get extremly large so outer-joins against the previously described views are again used to provide the lookup rather than lookup_ext()
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
89
7
7
Technical Implementation Voucher Summary section
Voucher Summary section The voucher summary section of the Accounts Payable Rapid Mart stores the voucher transactions for credit and debit memos, and their lines. The Rapid Mart calls the C_VoucherSummary_Section_PSF work flow to load dimension and fact tables from PeopleSoft. The following diagram shows the tables that the payment schedule section loads.
The Rapid Mart uses almost identical algorithms for the initial load and incremental load of the billing fact tables and also the dimension tables. The Rapid Mart runs the initial load when a variable $G_LOAD_TYPE is set to ‘FIRST’. When $G_LOAD_TYPE is set to ‘DELTA’, the incremental load will be executed. (See “Batch configuration variables” on page 78 for descriptions of variables used to initialize jobs.)
90
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Voucher Summary section
Dimension table load First, the section loads the required dimension components. If these dimensions have already been loaded during the current job, the job will not reload them. The section uses work flow components to load dimension tables. Work flow
Description
Tables loaded
C_TimeDim_PSF
Generated time periods with PeopleSoft calendar parameters with a granularity of a calendar day. Besides creating TIME_DIMENSION this work flow also creates MONTH_DIMENSION and FISCAL_CALENDAR at a monthly granularity. This workflow is shared with the
TIME_DIMENSION, MONTH_DIMENSIO N, FISCAL_CALENDAR
PeopleSoft HR and GL Rapid Marts
C_BusinessUnit_PSF
Business unit codes and descriptions. This BUSINESS_UNIT workflow is shared with the PeopleSoft HR
and GL Rapid Marts
C_UnitofMeasure_PSF
Unit of measure codes and descriptions
UNIT_OF_MEASUR E
C_PaymentTerms_PSF Payment terms attributes.
PAYMENT_TERMS
C_PymentDiscounts_P SF
Payment discount codes and descriptions
PAYMENT_DISCOU NTS
C_InvoiceItem_PSF
Invoice item codes and descriptions
INVOICE_ITEM
C_VendorAddress_PSF Vendor address data
VENDOR_ADDRESS
C_VendorType_PSF
Vendor type data
VENDOR_TYPE
C_Vendor_PSF
Vendor master data.
VENDOR
C_Currency_PSF
Currency abbreviations and texts.This CURRENCY workflow is shared with the PeopleSoft HR
Rapid Mart
For more details on extraction logic for dimensions, see annotations and object descriptions in the Rapid Mart WF_VoucherSummary_Dims_PSF workflow.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
91
7
7
Technical Implementation Voucher Summary section
Fact table load After loading the dimensions, the work flow determines whether you are loading the voucher summary fact tables for the first time. If you are, the Rapid Mart executes the initial load work flow. If not, the Rapid Mart executes the incremental load work flow. The initial load job extracts the voucher header and line item source data from the PeopleSoft tables. A staging table is used to separate the source PeopleSoft data extraction mechanism from the surrogate key lookup feature. This method maintains data extraction and transformation independence to ensure processing efficiency. The VOUCHER_FACT table is outer-joined with the VENDOR, VENDOR_ADDRESS and VENDOR_TYPE tables to provide the necessary surrogate key lookups. In a similar fashion the VOUCHER_LINE_FACT table is outer-joined with VENDOR, VENDOR_ADDRESS, VENDOR_TYPE and INVOICE_ITEM tables.
Incremental load Load dimension tables All dimensions excluding the time dimensions are delta loaded based upon a table comparison between the current dimension source information and the dimension records previously loaded. The time dimensions are always fully re-loaded.
Load fact table The same staging table methodology employed by the standard fact table load is used when loading data incrementally. The following procedures are used to populate VOUCHER_FACT and VOUCHER_LINE_FACT during incremental loads: 1.
Staging tables STAGE_VOUCHER_FACT and STAGE_VOUCHER_LINE_FACT are loaded in TRUNCATE mode with results of a 2-way inner-join where the voucher table LAST_UPDATE_DT field has the following constraint: $G_SDATE = LAST_UPDATE_DT
2.
92
STAGE_VOUCHER_FACT and STAGE_VOUCHER_LINE_FACT are used as the input to renaming functions where the lookup_ext() function determines the surrogate key identifier from the relevant dimension tables.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Voucher Distribution Line section
Voucher Distribution Line section The voucher distribution line section of the Accounts Payable Rapid Mart stores all accounting distribution line transactions The Rapid Mart calls the C_VoucherDistribution_Section_PSF work flow to load dimension and fact tables from PeopleSoft. The following diagram shows the tables that the payment schedule section loads.
The Rapid Mart uses almost identical algorithms for the initial load and incremental load of the distribution line fact tables and also the dimension tables. The Rapid Mart runs the initial load when a variable $G_LOAD_TYPE is set to ‘FIRST’. When $G_LOAD_TYPE is set to ‘DELTA’, the incremental load will be executed. (See “Batch configuration variables” on page 78 for descriptions of variables used to initialize jobs.)
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
93
7
7
Technical Implementation Voucher Distribution Line section
Dimension table load First, the section loads the required dimension components. If these dimensions have already been loaded during the current job, the job will not reload them. The section uses work flow components to load dimension tables. Work flow
Description
Tables loaded
C_TimeDim_PSF
Generated time periods with PeopleSoft TIME_DIMENSION, calendar parameters with a granularity of MONTH_DIMENSION, a calendar day. Besides creating FISCAL_CALENDAR TIME_DIMENSION this work flow also creates MONTH_DIMENSION and FISCAL_CALENDAR at a monthly granularity. This workflow is shared with the PeopleSoft HR and GL Rapid Marts
C_BusinessUnit_PSF
Business unit codes and descriptions. This workflow is shared with the
BUSINESS_UNIT
C_Account_PSF
Account codes and descriptions. This dimension is shared with the PeopleSoft GL Rapid Mart
ACCOUNT
C_AltAccount_PSF
Alternate account codes and descriptions. This dimension is shared with the PeopleSoft GL Rapid Mart
ALTACCOUNT
PeopleSoft HR and GL Rapid Marts
C_OperatingUnit_PSF Operating Unit codes and descriptions. This dimension is shared with the PeopleSoft GL Rapid Mart
OPERATING_UNIT
C_Company_PSF
Company codes and descriptions.This dimension is shared with the PeopleSoft GL Rapid Mart
COMPANY
C_Department_PSF
Department codes and descriptions. This DEPARTMENT workflow is shared with the PeopleSoft
HR and GL Rapid Marts
C_UnitofMeasure_PS F
Unit of measure codes and descriptions
C_PaymentTerms_PS Payment terms attributes. F C_PymentDiscounts_ PSF
94
UNIT_OF_MEASURE PAYMENT_TERMS
Payment discount codes and descriptions PAYMENT_DISCOUNTS
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Voucher Distribution Line section
Work flow
Description
Tables loaded
C_InvoiceItem_PSF
Invoice item codes and descriptions
INVOICE_ITEM
C_VendorAddress_PS Vendor address data F
VENDOR_ADDRESS
C_VendorType_PSF
Vendor type data
VENDOR_TYPE
C_Vendor_PSF
Vendor master data.
VENDOR
C_Currency_PSF
Currency abbreviations and texts.This workflow is shared with the PeopleSoft
CURRENCY
Hierarchy dimensions for Account, Alternate Account, Department and Operating Units
ACCOUNT_HIER_PC ACCOUNT_HIER_HZ ACCOUNT_HIER_VR ALTACCOUNT_HIER_PC ALTACCOUNT_HIER_HZ ALTACCOUNT_HIER_VR DEPARTMENT_HIER_P C DEPARTMENT_HIER_H Z DEPARTMENT_HIER_V R OPERATING_UNIT_HIE R_PC OPERATING_UNIT_HIE R_HZ OPERATING_UNIT_HIE R_VR
HR Rapid Mart
C_APHierarchy_PSF
The dimensions affected by the control table lookup for Voucher Distribution Line (i.e. those that incorporate a SETID column in the dimension) are as follows:
• • • • •
ACCOUNT ALTACCOUNT DEPARTMENT OPERATING_UNIT PAYMENT_TERMS
Please refer to the section “Use of surrogate keys in this Rapid Mart” for more information on the SETID control record and surrogate key generation within this Rapid Mart.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
95
7
7
Technical Implementation Voucher Distribution Line section
For more details on extraction logic for dimensions, see annotations and object descriptions in the Rapid Mart WF_VoucherDistribution_Dims_PSF workflow.
Fact table load After loading the dimensions, the work flow determines whether you are loading the voucher distribution fact table for the first time. If you are, the Rapid Mart executes the initial load work flow. If not, the Rapid Mart executes the incremental load work flow. The initial load job extracts the voucher distribution source data from the PeopleSoft table. Staging tables are used to separate the source PeopleSoft data extraction mechanism from the surrogate key lookup feature. This method maintains data extraction and transformation independence to ensure processing efficiency. The DISTRIB_LINE_FACT table is outer-joined with the VENDOR, VENDOR_ADDRESS and VENDOR_TYPE tables to provide the necessary surrogate key lookups.
Incremental load Load dimension tables All dimensions excluding the time dimensions are delta loaded based upon a table comparison between the current dimension source information and the dimension records previously loaded. The time dimensions are always fully re-loaded.
Load fact table The same staging table methodology employed by the standard fact table load is used when loading data incrementally. The following procedures are used to populate DISTRIB_LINE_FACT during incremental loads: 1.
Staging table STAGE_DISTRIB_LINE_FACT is loaded in TRUNCATE mode with results of a 2-way inner-join where the payment schedule table LAST_UPDATE_DT field has the following constraint: $G_SDATE = LAST_UPDATE_DT
2.
96
STAGE_PYMT_DISTRIB_LINE_FACT is used as the input to renaming functions where the lookup_ext() function determine the surrogate key identifier from the relevant dimension tables.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Open Liability section
Open Liability section The open liability section of the Accounts Payable Rapid Mart stores the details of outstanding unpaid vouchers. The Rapid Mart calls the C_OpenLiability_Section_PSF work flow to load receipt dimension and fact tables from PeopleSoft. The following diagram shows the tables that the open liability section loads.
The Rapid Mart uses almost identical algorithms for the initial load and incremental load of the open liability fact tables and also the dimension tables. The Rapid Mart runs the initial load when a variable $G_LOAD_TYPE is set to ‘FIRST’. When $G_LOAD_TYPE is set to ‘DELTA’, the incremental load will be executed. (See “Batch configuration variables” on page 78 for descriptions of variables used to initialize jobs.)
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
97
7
7
Technical Implementation Open Liability section
Dimension table load First, the section loads the required dimension components. If these dimensions have already been loaded during the current job, the job will not reload them. The section uses work flow components to load dimension tables. Work flow
Description
Tables loaded
C_TimeDim_PSF
Generated time periods with PeopleSoft calendar parameters with a granularity of a calendar day. Besides creating TIME_DIMENSION this work flow also creates MONTH_DIMENSION and FISCAL_CALENDAR at a monthly granularity. This workflow is shared with the
TIME_DIMENSION, MONTH_DIMENSIO N, FISCAL_CALENDAR
Business unit codes and descriptions. This workflow is shared with the PeopleSoft HR
BUSINESS_UNIT
Payment terms attributes.
PAYMENT_TERMS
PeopleSoft HR and GL Rapid Marts
C_BusinessUnit_PSF
and GL Rapid Marts C_PaymentTerms_PS F
C_PymentDiscounts_P Payment discount codes and descriptions SF
PAYMENT_DISCOUN TS
C_VendorAddress_PS Vendor address data F
VENDOR_ADDRESS
C_VendorType_PSF
Vendor type data
VENDOR_TYPE
C_Vendor_PSF
Vendor master data.
VENDOR
C_Currency_PSF
Currency abbreviations and texts.This workflow is shared with the PeopleSoft HR
CURRENCY
Rapid Mart
For more details on extraction logic for dimensions, see annotations and object descriptions in the Rapid Mart WF_OpenLiability_Dims_PSF workflow.
98
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Open Liability section
Fact table load After loading the dimensions, the work flow determines whether you are loading the open liability fact table for the first time. If you are, the Rapid Mart executes the initial load work flow. If not, the Rapid Mart executes the incremental load work flow. The initial load job extracts the open liability source data from the PeopleSoft transaction table. A staging table is used to separate the source PeopleSoft data extraction mechanism from the surrogate key lookup feature. This method maintains data extraction and transformation independence to ensure processing efficiency. The OPEN_LIAB_FACT table is outer-joined with the VENDOR table to provide the necessary surrogate key lookups.
Incremental load Load dimension tables All dimensions excluding the time dimensions are delta loaded based upon a table comparison between the current dimension source information and the dimension records previously loaded. The time dimensions are always fully re-loaded.
Load fact table The same staging table methodology employed by the standard fact table load is used when loading data incrementally. The following procedures are used to populate OPEN_LIAB_FACT during incremental loads: 1.
Staging table STAGE_OPEN_LIAB_FACT is loaded in TRUNCATE mode where the open liabilities table AS_OF_DATE field has the following constraint: $G_SDATE = AS_OF_DATE
2.
STAGE_OPEN_LIAB_FACT is used as the input to renaming functions where the lookup_ext() function determine the surrogate key identifier from the relevant dimension tables.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
99
7
7
Technical Implementation Payment section
Payment section The payment section of the Accounts Payable Rapid Mart stores the details of invoice payments made to suppliers. The Rapid Mart calls the C_InvoicePayment_Section_PSF work flow to load receipt dimension and fact tables from PeopleSoft. The following diagram shows the tables that the open liability section loads.
The Rapid Mart uses almost identical algorithms for the initial load and incremental load of the invoice payment fact table and also the dimension tables. The Rapid Mart runs the initial load when a variable $G_LOAD_TYPE is set to ‘FIRST’. When $G_LOAD_TYPE is set to ‘DELTA’, the incremental load will be executed. (See “Batch configuration variables” on page 78 for descriptions of variables used to initialize jobs.)
100
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Payment section
Dimension table load First, the section loads the required dimension components. If these dimensions have already been loaded during the current job, the job will not reload them. The section uses work flow components to load dimension tables. Work flow
Description
Tables loaded
C_TimeDim_PSF
Generated time periods with PeopleSoft calendar parameters with a granularity of a calendar day. Besides creating TIME_DIMENSION this work flow also creates MONTH_DIMENSION and FISCAL_CALENDAR at a monthly granularity. This workflow is shared with the
TIME_DIMENSION, MONTH_DIMENSIO N, FISCAL_CALENDAR
Business unit codes and descriptions. This workflow is shared with the PeopleSoft HR
BUSINESS_UNIT
PeopleSoft HR and GL Rapid Marts
C_BusinessUnit_PSF
and GL Rapid Marts
C_VendorAddress_PS Vendor address data F
VENDOR_ADDRESS
C_VendorType_PSF
Vendor type data
VENDOR_TYPE
C_Vendor_PSF
Vendor master data.
VENDOR
C_Currency_PSF
Currency abbreviations and texts.This workflow is shared with the PeopleSoft HR
CURRENCY
Rapid Mart
For more details on extraction logic for dimensions, see annotations and object descriptions in the Rapid Mart WF_InvoicePayment_Dims_PSF workflow.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
101
7
7
Technical Implementation Payment section
Fact table load After loading the dimensions, the work flow determines whether you are loading the invoice payment fact table for the first time. If you are, the Rapid Mart executes the initial load work flow. If not, the Rapid Mart executes the incremental load work flow. The initial load job extracts the invoice payment source data from the PeopleSoft transaction table. A staging table is used to separate the source PeopleSoft data extraction mechanism from the surrogate key lookup feature. This method maintains data extraction and transformation independence to ensure processing efficiency. The AP_PAYMENT_FACT table is outer-joined with the VENDOR and VENDOR_TYPE tables to provide the necessary surrogate key lookups.
Incremental load Load dimension tables All dimensions excluding the time dimensions are delta loaded based upon a table comparison between the current dimension source information and the dimension records previously loaded. The time dimensions are always fully re-loaded.
Load fact table The same staging table methodology employed by the standard fact table load is used when loading data incrementally. The following procedures are used to populate AP_PAYMENT_FACT during incremental loads: 1.
Staging table STAGE_AP_PAYMENT_FACT is loaded in TRUNCATE mode where the payment table CREATION_DT field has the following constraint: $G_SDATE = CREATION_DT
2.
102
STAGE_AP_PAYMENT_FACT is used as the input to renaming functions where the lookup_ext() function determine the surrogate key identifier from the relevant dimension tables.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Technical Implementation Dealing with NULL values
Dealing with NULL values As the first step in data cleansing, the BusinessObjects PeopleSoft Accounts Payable Rapid Mart allows replacing NULL values found in the source system with configurable default ‘not-null’ values. The Accounts Payable Rapid Mart provides three category of default values to treat NULLs:
•
For columns that behave as a foreign key the Accounts Payable Rapid Mart replaces NULL value with a global variable $G_DEFAULT_NUMBER of data type integer;
•
For columns that store descriptions NULLs are replaced with $G_DEFAULT_TEXT of data type char(1);
•
For columns that store dates NULLs are replaced with $G_DEFAULT_DATE of data type DATE.
Dimensions whose corresponding foreign keys in the fact tables can have value NULL are loaded with an additional ‘dummy’ row to represent NULL. The value of $G_DEFAULT_NUMBER global variable gets inserted into each dimension primary key column.
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
103
7
7
Technical Implementation Fact table stored procedures
Fact table stored procedures When loading each fact table the Rapid Mart calls two stored procedures. A PREPROCESSING_FACT_TABLE procedure is called before loading the fact table and a POSTPROCESSING_FACT_TABLE procedure is called when the load is finished. The procedures are automatically installed by the Rapid Mart only as placeholders: CREATE OR REPLACE PROCEDURE postprocessing_fact_table(pLoadType IN VARCHAR2, pDropCreateIndex IN VARCHAR2, pFactTable IN VARCHAR2, pMView_Failures OUT BINARY_INTEGER) IS BEGIN NULL; END postprocessing_fact_table; CREATE OR REPLACE PROCEDURE preprocessing_fact_table(pLoadType IN VARCHAR2, pDropCreateIndex IN VARCHAR2, pFactTable IN VARCHAR2) IS BEGIN NULL; END preprocessing_fact_table;
In order to use these procedures as a performance tuning tool, you need to customize them. The Rapid Mart provides a sample script Create_Sample_Maintenance_SP.sql with necessary modifications. The PREPROCESSING_FACT_TABLE Oracle stored procedure defined in the sample script will drop all indexes and all materialized view logs enabled and POSTPROCESSING_FACT_TABLE will recreate them. See the Rapid Mart Deployment Guide for details.
104
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
appendix
Rapid Mart Data Schema
A
Rapid Mart Data Schema Overview
Overview This appendix provides detailed information about the tables and views in the BusinessObjects PeopleSoft Accounts Payable Rapid Mart. This information applies to an Oracle target datastore.
Fact tables in the Rapid Mart • • • • •
AP_PAYMENT_FACT DISTRIB_LINE_FACT OPEN_LIAB_FACT VOUCHER_FACT VOUCHER_LINE_FACT
Dimension tables in the Rapid Mart • • • • • • • • • • • • • • • • • • •
106
ACCOUNT ACCOUNT_HIER_HZ ACCOUNT_HIER_PC ACCOUNT_HIER_VR ALTACCOUNT ALTACCOUNT_HIER_HZ ALTACCOUNT_HIER_PC ALTACCOUNT_HIER_VR AP_AGING AP_PAYMENT_DISCOUNTS AP_PAYMENT_TERMS BUSINESS_UNIT COMPANY CURRENCY DEPARTMENT DEPARTMENT_HIER_HZ DEPARTMENT_HIER_PC DEPARTMENT_HIER_VR FISCAL_CALENDAR
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema Other tables in the Rapid Mart
• • • • • • • • • • • •
INVOICE_ITEM MONTH_DIMENSION OPERATING_UNIT OPERATING_UNIT_HIER_HZ OPERATING_UNIT_HIER_PC OPERATING_UNIT_HIER_VR TIME_DIMENSION UNIT_OF_MEASURE UNIT_OF_MEASURE VENDOR_ADDRESS VENDOR_TYPE WITHHOLDING
Other tables in the Rapid Mart • • • • • • • • • • • • •
AW_JOBEXECUTION LOOKUP_INDEXES PS_AP_SET_CNTRL_REC STAGE_AP_PAYMENT_FACT STAGE_CAL_PERIODS STAGE_DISTRIB_LINE_FACT STAGE_OPEN_LIAB_FACT STAGE_TIME_DIM STAGE_VOUCHER_FACT STAGE_VOUCHER_LINE_FACT TEMP_AP_PAYMENT_DISCOUNTS TEMP_DEPARTMENT TEMP_VENDOR
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
107
A
A
Rapid Mart Data Schema ACCOUNT
ACCOUNT Dimension with Account attributes Column name
Key
Data type
Column description
ACCOUNT_KEY
PK
VARCHAR2(30) Surrogate key to ACCOUNT
SETID
VARCHAR2(15) SetID is used to differentiate between Account values that belong to different Business Units
ACCOUNT
VARCHAR2(30) Account code
EFFDT
DATETIME
Effective from date
EFF_STATUS
VARCHAR2(1)
Effective status (Active or Inactive)
DESCR
VARCHAR2(90) Account long description
DESCRSHORT
VARCHAR2(30) Account short description
UNIT_OF_MEASURE
VARCHAR2(9)
Unit of measure
CURRENCY_CD
VARCHAR2(9)
Account currency code
STATISTICS_ACCOUN T
VARCHAR2(3)
Statistics account indicator
BALANCE_FWD_SW
VARCHAR2(3)
Account balance forward indicator
TO_EFFDT
DATETIME
Effective to date
ACCOUNT_TYPE
VARCHAR2(3)
Account type
BSHEET_IND_DESCR
VARCHAR2(30) Balance sheet indicator
VAT_ACCT_FLG_DESC R
VARCHAR2(30) VAT account description
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
108
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema ACCOUNT_HIER_HZ
ACCOUNT_HIER_HZ Natural Account key horizontally flattened hierarchy. Column name
Key
Data type
Column description
SETID
PK
VARCHAR2(10) SetID is used to differentiate between Account values that belong to different Business Units
ACCOUNT
PK
VARCHAR2(30) Current leaf Account value
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1)
Effective date current date indicator
LEAF_LEVEL
NUMBER
Leaf level in horizontal flattening
MAXDEPTH
NUMBER
Maximum depth of hierarchy
LEVEL0_ACCOUNT
VARCHAR2(30) Level0 (root) value for the current leaf of Account
LEVEL0_ACCOUNT_KE Y
NUMBER
LEVEL0_DESCRIPTION
VARCHAR2(90) Level0 (root) description for the current leaf of Account
LEVEL1_ACCOUNT
VARCHAR2(30) Level1 value for the current leaf of Account
LEVEL1_ACCOUNT_KE Y
NUMBER
LEVEL1_DESCRIPTION
VARCHAR2(90) Level1 description for the current leaf of Account
LEVEL2_ACCOUNT
VARCHAR2(30) Level2 value for the current leaf of Account
LEVEL2_ACCOUNT_KE Y
NUMBER
LEVEL2_DESCRIPTION
VARCHAR2(90) Level2 description for the current leaf of Account
LEVEL3_ACCOUNT
VARCHAR2(30) Level3 value for the current leaf of Account
LEVEL3_ACCOUNT_KE Y
NUMBER
Level0 (root) surrogate key for the current leaf of Account
Level1 surrogate key for the current leaf of Account
Level2 surrogate key for the current leaf of Account
Level3 surrogate key for the current leaf of Account
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
109
A
A
Rapid Mart Data Schema ACCOUNT_HIER_HZ
Column name
Key
Data type
Column description
LEVEL3_DESCRIPTION
VARCHAR2(90) Level3 description for the current leaf of Account
LEVEL4_ACCOUNT
VARCHAR2(30) Level4 value for the current leaf of Account
LEVEL4_ACCOUNT_KE Y
NUMBER
LEVEL4_DESCRIPTION
VARCHAR2(90) Level4 description for the current leaf of Account
LEVEL5_ACCOUNT
VARCHAR2(30) Level5 value for the current leaf of Account
LEVEL5_ACCOUNT_KE Y
NUMBER
LEVEL5_DESCRIPTION
VARCHAR2(90) Level5 description for the current leaf of Account
LEVEL6_ACCOUNT
VARCHAR2(30) Level6 value for the current leaf of Account
LEVEL6_ACCOUNT_KE Y
NUMBER
LEVEL6_DESCRIPTION
VARCHAR2(90) Level6 description for the current leaf of Account
LEVEL7_ACCOUNT
VARCHAR2(30) Level7 value for the current leaf of Account
LEVEL7_ACCOUNT_KE Y
NUMBER
LEVEL7_DESCRIPTION
VARCHAR2(90) Level7 description for the current leaf of Account
LEVEL8_ACCOUNT
VARCHAR2(30) Level8 value for the current leaf of Account
LEVEL8_ACCOUNT_KE Y
NUMBER
LEVEL8_DESCRIPTION
VARCHAR2(90) Level8 description for the current leaf of Account
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
110
Level4 surrogate key for the current leaf of Account
Level5 surrogate key for the current leaf of Account
Level6 surrogate key for the current leaf of Account
Level7 surrogate key for the current leaf of Account
Level8 surrogate key for the current leaf of Account
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema ACCOUNT_HIER_PC
ACCOUNT_HIER_PC Natural Account key parent/child relationship hierarchy Column name
Key Data type
ACCOUNT_KEY
NUMBER
Column description Surrogate key to child Account
SETID
PK
VARCHAR2(1 SetID is used to differentiate between 5) Account values that belong to different Business Units
ACCOUNT
PK
VARCHAR2(3 Child Account 0)
DESCRIPTION EFFECTIVE_FM_DT
VARCHAR2(9 Child Account description 0) PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1 Effective date current date indicator )
PARENT_ACCOUNT_KEY
NUMBER
PARENT_ACCOUNT
VARCHAR2(3 Parent Account 0)
PARENT_DESCRIPTION
VARCHAR2(9 Parent Account description 0)
LOAD_DATE
DATETIME
LOAD_TIME
VARCHAR2(8 Time when the record was loaded into ) the target system
Parent Account surrogate key
Date and Time when the record was loaded into target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
111
A
A
Rapid Mart Data Schema ACCOUNT_HIER_VR
ACCOUNT_HIER_VR Natural Account key vertically flattened hierarchy Column name
Key Data type
SETID
PK
VARCHAR2(1 SetID is used to differentiate between 5) Account values that belong to different Business Units
ANCESTOR_ACCOUNT
PK
VARCHAR2(3 Ancestor Account code 0)
DESCENDENT_ACCOUNT
PK
VARCHAR2(3 Descendent Account code 0)
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1 Effective date current date indicator )
MAXDEPTH
NUMBER
Maximum depth of hierarchy
DEPTH
NUMBER
Depth of the descendent
ROOT_FLAG
NUMBER
Root flag (1 means this is a root)
LEAF_FLAG
NUMBER
Leaf flag (1 means this is a leaf)
TREELEVEL
NUMBER
Tree level
ANCESTOR_ACCOUNT_KEY
NUMBER
Ancestor Account surrogate key
ANCESTOR_ACCOUNT_DES CR
VARCHAR2(9 Ancestor Account description 0)
DESCENDENT_ACCOUNT_K EY
NUMBER
DESCENDENT_ACCOUNT_D ESCR
VARCHAR2(9 Descendent Account description 0)
LOAD_DATE
DATETIME
LOAD_TIME
VARCHAR2(8 Time when the record was loaded into ) the target system
112
Column description
Descendent Account surrogate key
Date and Time when the record was loaded into target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema ALTACCOUNT
ALTACCOUNT Dimension with Alternate Account attributes Column name
Key
Data type
Column description
ALTACCOUNT_KEY
PK
NUMBER
Surrogate key to ALTACCOUNT
SETID
VARCHAR2(15)
SetID is used to differentiate between Alternate Account values that belong to different Business Units
ALTACCOUNT
VARCHAR2(30)
Alternate account code
EFFDT
DATETIME
Effective from date
EFF_STATUS
VARCHAR2(1)
Effective Status
DESCR
VARCHAR2(90)
Alternate account long description
DESCRSHORT
VARCHAR2(30)
Alternate account short description
UNIT_OF_MEASURE
VARCHAR2(9)
Unit of measure
STATISTICS_ACCOUN T
VARCHAR2(3)
Statistics account indicator
BALANCE_FWD_SW
VARCHAR2(3)
Alternate account balance forward indicator
BSHEET_IND_DESCR
VARCHAR2(30)
Balance sheet indicator
ACCOUNT_TYPE
VARCHAR2(3)
Account type
TO_EFFDT
DATETIME
Effective to date
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
113
A
A
Rapid Mart Data Schema ALTACCOUNT_HIER_HZ
ALTACCOUNT_HIER_HZ Natural Alternate Account key horizontally flattened hierarchy. Column name
Key
Data type
SETID
PK
VARCHAR2(10) SetID is used to differentiate between Alternate Account values that belong to different Business Units
ALTACCOUNT
PK
VARCHAR2(30) Current leaf Alternate Account value
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1)
Effective date current date indicator
LEAF_LEVEL
NUMBER
Leaf level in horizontal flattening
MAXDEPTH
NUMBER
Maximum depth of hierarchy
LEVEL0_ALTACCOUNT
VARCHAR2(30) Level0 (root) value for the current leaf of Alternate Account
LEVEL0_ALTACCOUNT_ KEY
NUMBER
LEVEL0_DESCRIPTION
VARCHAR2(90) Level0 (root) description for the current leaf of Alternate Account
LEVEL1_ALTACCOUNT
VARCHAR2(30) Level1 value for the current leaf of Alternate Account
LEVEL1_ALTACCOUNT_ KEY
NUMBER
LEVEL1_DESCRIPTION
VARCHAR2(90) Level1 description for the current leaf of Alternate Account
LEVEL2_ALTACCOUNT
VARCHAR2(30) Level2 value for the current leaf of Alternate Account
LEVEL2_ALTACCOUNT_ KEY
NUMBER
LEVEL2_DESCRIPTION
VARCHAR2(90) Level2 description for the current leaf of Alternate Account
LEVEL3_ALTACCOUNT
VARCHAR2(30) Level3 value for the current leaf of Alternate Account
LEVEL3_ALTACCOUNT_ KEY
NUMBER
114
Column description
Level0 (root) surrogate key for the current leaf of Alternate Account
Level1 surrogate key for the current leaf of Alternate Account
Level2 surrogate key for the current leaf of Alternate Account
Level3 surrogate key for the current leaf of Alternate Account
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema ALTACCOUNT_HIER_HZ
Column name
Key
Data type
Column description
LEVEL3_DESCRIPTION
VARCHAR2(90) Level3 description for the current leaf of Alternate Account
LEVEL4_ALTACCOUNT
VARCHAR2(30) Level4 value for the current leaf of Alternate Account
LEVEL4_ALTACCOUNT_ KEY
NUMBER
LEVEL4_DESCRIPTION
VARCHAR2(90) Level4 description for the current leaf of Alternate Account
LEVEL5_ALTACCOUNT
VARCHAR2(30) Level5 value for the current leaf of Alternate Account
LEVEL5_ALTACCOUNT_ KEY
NUMBER
LEVEL5_DESCRIPTION
VARCHAR2(90) Level5 description for the current leaf of Alternate Account
LEVEL6_ALTACCOUNT
VARCHAR2(30) Level6 value for the current leaf of Alternate Account
LEVEL6_ALTACCOUNT_ KEY
NUMBER
LEVEL6_DESCRIPTION
VARCHAR2(90) Level6 description for the current leaf of Alternate Account
LEVEL7_ALTACCOUNT
VARCHAR2(30) Level7 value for the current leaf of Alternate Account
LEVEL7_ALTACCOUNT_ KEY
NUMBER
LEVEL7_DESCRIPTION
VARCHAR2(90) Level7 description for the current leaf of Alternate Account
LEVEL8_ALTACCOUNT
VARCHAR2(30) Level8 value for the current leaf of Alternate Account
LEVEL8_ALTACCOUNT_ KEY
NUMBER
LEVEL8_DESCRIPTION
VARCHAR2(90) Level8 description for the current leaf of Alternate Account
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
Level4 surrogate key for the current leaf of Alternate Account
Level5 surrogate key for the current leaf of Alternate Account
Level6 surrogate key for the current leaf of Alternate Account
Level7 surrogate key for the current leaf of Alternate Account
Level8 surrogate key for the current leaf of Alternate Account
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
115
A
A
Rapid Mart Data Schema ALTACCOUNT_HIER_PC
ALTACCOUNT_HIER_PC Natural Alternate Account key parent/child relationship hierarchy Column name
Key Data type
ALTACCOUNT_KEY
NUMBER
Column description Surrogate key to child Alternate Account
SETID
PK
VARCHAR2(1 SetID is used to differentiate between 5) Alternate Account values that belong to different Business Units
ALTACCOUNT
PK
VARCHAR2(3 Child Alternate Account 0)
DESCRIPTION
VARCHAR2(9 Child Alternate Account description 0)
EFFECTIVE_FM_DT
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1 Effective date current date indicator )
PARENT_ALTACCOUNT_KE Y
NUMBER
PARENT_ALTACCOUNT
VARCHAR2(3 Parent Alternate Account 0)
PARENT_DESCRIPTION
VARCHAR2(9 Parent Alternate Account description 0)
LOAD_DATE
DATETIME
LOAD_TIME
VARCHAR2(8 Time when the record was loaded into ) the target system
116
PK
Parent Alternate Account surrogate key
Date and Time when the record was loaded into target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema ALTACCOUNT_HIER_VR
ALTACCOUNT_HIER_VR Natural Alternate Account key vertically flattened hierarchy Column name
Key
Data type
Column description
SETID
PK
VARCHAR2(1 5)
SetID is used to differentiate between Alternate Account values that belong to different Business Units
ANCESTOR_ALTACCOUNT
PK
VARCHAR2(3 0)
Ancestor Alternate Account code
DESCENDENT_ALTACCOUNT
PK
VARCHAR2(3 0)
Descendent Alternate Account code
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1) Effective date current date indicator
MAXDEPTH
NUMBER
Maximum depth of hierarchy
DEPTH
NUMBER
Depth of the descendent
ROOT_FLAG
NUMBER
Root flag (1 means this is a root)
LEAF_FLAG
NUMBER
Leaf flag (1 means this is a leaf)
TREELEVEL
NUMBER
Tree level
ANCESTOR_ALTACCOUNT_KE Y
NUMBER
Ancestor Alternate Account surrogate key
ANCESTOR_ALTACCOUNT_D ESCR
VARCHAR2(9 0)
Ancestor Alternate Account description
DESCENDENT_ALTACCOUNT _KEY
NUMBER
Descendent Alternate Account surrogate key
DESCENDENT_ALTACCOUNT _DESCR
VARCHAR2(9 0)
Descendent Alternate Account description
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8) Time when the record was loaded into the target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
117
A
A
Rapid Mart Data Schema AP_AGING
AP_AGING Dimension with Aging Category attributes Column name
Key
Data type
Column description
AP_AGING_KEY
PK
NUMBER
Surrogate key to AP_AGING
AP_AGING_CYCLE_ID
VARCHAR2(15)
Aging cycle ID
AP_AGING_AGE_BUCKET
INTEGER
Aging category
DESCR
VARCHAR2(90)
Description
DAY_INCREMENT1
INTEGER
Day increment 1
DAY_INCREMENT2
INTEGER
Day increment 2
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
AP_PAYMENT_DISCOUNTS Dimension with Payment Discountattribute. Column name
Key
Data type
Column description
AP_PAYMENT_DISCOUNT PK S_KEY
NUMBER
Surrogate key to AP_PAYMENT_DISCOUNTS
AP_PAYMENT_TERMS_KE Y
NUMBER
Surrogate key to AP_PAYMENT_TERMS
SETID
VARCHAR2(15)
SetID is used to differentiate between payment terms values that belong to different Business Units
PAYMNT_TERMS_CD
VARCHAR2(15)
Payment Terms ID
EFFDT
DATE
Effective from date
NET_TRMS_SEQ_NBR
INTEGER
Effective status (Active or Inactive)
DSCNT_TRMS_SEQ_NBR
INTEGER
Discount terms sequence number
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
TO_EFFDT
DATE
Effective to date
BASIS_FROM_DAY
INTEGER
Basis from date
BASIS_TO_DAY
INTEGER
Basis to date
118
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema AP_PAYMENT_FACT
Column name
Key
Data type
Column description
DESCR
VARCHAR2(90)
Description
DESCRSHORT
VARCHAR2(30)
Short description
TMG_BASIS_OPTION_DE SCR
VARCHAR2(30)
Timing basis option description
TMG_REL_MONTH_VAL
INTEGER
End of relative month
TMG_MONTH_DUE_CD
VARCHAR2(6)
Month due
TMG_DAY_DUE_CD
VARCHAR2(6)
Day due
DUE_DT
DATE
Due date
TMG_DAY_INCR_VAL
INTEGER
Day increment
TMG_MONTH_INCR_VAL
INTEGER
Month increment
TMG_YEAR_INCR_VAL
INTEGER
Year increment
DSCNT_TRMS_TIME_ID
VARCHAR2(9)
Discount terms timing ID
TERMS_ADJUST_DAY
INTEGER
Terms adjustment days
DSCNT_TRMS_PERCENT
NUMBER(11,8)
Discount terms percent
DSCNT_TRMS_AMOUNT
NUMBER(16,3)
Discount terms amount
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
AP_PAYMENT_FACT Invoice payments transaction data Column name
Key
Data type
Column description
BANK_SETID
VARCHAR2(15)
Bank setID
BANK_CD
VARCHAR2(15)
Bank code
BANK_ACCT_KEY
VARCHAR2(12)
Bank account
PYMNT_ID
VARCHAR2(30)
Payment number
PYMNT_ID_REF
VARCHAR2(60)
Payment reference
VENDOR_KEY
NUMBER
Surrogate key to the VENDOR dimension
VENDOR_TYPE_KEY
NUMBER
Surrogate key to the VENDOR_TYPE dimension
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
119
A
A
Rapid Mart Data Schema AP_PAYMENT_FACT
Column name
Key
Data type
Column description
EMPLID
VARCHAR2(33)
Employee ID
NAME1
VARCHAR2(120)
Vendor name
NAME2
VARCHAR2(120)
Vendor name additional description
COUNTRY
VARCHAR2(9)
Country
ADDRESS1
VARCHAR2(165)
Address line 1
ADDRESS2
VARCHAR2(165)
Address line 2
ADDRESS3
VARCHAR2(165)
Address line 3
ADDRESS4
VARCHAR2(165)
Address line 4
CITY
VARCHAR2(90)
City
HOUSE_TYPE
VARCHAR2(6)
House type
ADDR_FIELD1
VARCHAR2(6)
Address field 1
ADDR_FIELD2
VARCHAR2(12)
Address field 2
ADDR_FIELD3
VARCHAR2(12)
Address field 3
COUNTY
VARCHAR2(90)
County
STATE
VARCHAR2(18)
State
POSTAL
VARCHAR2(36)
Postal code
GEO_CODE
VARCHAR2(33)
Tax vendor geographical code
PYMNT_DT
DATE
Payment date
CREATION_DT
DATE
Creation date
OPRID
VARCHAR2(90)
User ID
PYMNT_AMT
NUMBER(26,3)
Payment amount
CURRENCY_PYMNT_K EY
NUMBER
Surrogate key to the CURRENCY dimension
PYMNT_METHOD_DES CR
VARCHAR2(30)
Payment method description
PYMNT_STATUS_DES CR
VARCHAR2(30)
Payment status description
CANCEL_ACTION_DES CR
VARCHAR2(30)
Cancel action description
CANCEL_DT
DATE
Cancellation date
ACCOUNTING_DT
DATE
Accounting date
POST_STATUS_AP_DE SCR
VARCHAR2(30)
Post status decription
120
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema AP_PAYMENT_TERMS
Column name
Key
Data type
Column description
RECON_STATUS_DES CR
VARCHAR2(30)
Recon status description
PYMNT_TYPE_DESCR
VARCHAR2(30)
Payment type description
BUSINESS_UNIT_GL_K EY
NUMBER
Surrogate key to the BUSINESS_UNIT dimension
TIME_KEY
NUMBER
Surrogate key to TIME_DIMENSION
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
AP_PAYMENT_TERMS Dimension with Payment Terms attributes Column name
Key
Data type
Column description
AP_PAYMENT_TERMS_KEY PK
NUMBER
Surrogate key to AP_PAYMENT_TERMS
SETID
VARCHAR2(15) SetID is used to differentiate between payment terms values that belong to different business units
PAYMNT_TERMS_CD
VARCHAR2(15) Payment Terms ID
EFFDT
DATE
Effective from date
EFF_STATUS
VARCHAR2(3)
Effective status (Active or Inactive)
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
TO_EFFDT
DATE
Effective to date
PYMNT_TERMS_TYPE_DES CR
VARCHAR2(30) Payment terms type description
DESCR
VARCHAR2(90) Payment Terms description
DESCRSHORT
VARCHAR2(30) Payment Terms short description
PYMNT_TERMS_APPLIC_D ESCR
VARCHAR2(30) Payment Terms applicability description
NBR_OF_TERMS
INTEGER
Number of terms
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
121
A
A
Rapid Mart Data Schema AW_JOBEXECUTION
Column name
Key
Data type
Column description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
AW_JOBEXECUTION Data Integrator internal table to trace job execution status Column name
Key
Data type
Column description
NAME
PK
VARCHAR2(64) Job name
STATUS
VARCHAR2(12) Job run status
EXTRACTLOW
VARCHAR2(10) Extract start date
EXTRACTHIGH
VARCHAR2(10) Extract end date
BUSINESS_UNIT Dimension with Business Unit attributes Column name
Key
Data type
Column description
BUSINESS_UNIT_KEY
PK
NUMBER
Surrogate key to BUSINESS_UNIT
BUSINESS_UNIT
VARCHAR2(15)
Business unit code
BASE_CURRENCY_KE Y
NUMBER
Surrogate key to base currency
DESCR
VARCHAR2(90)
Business unit long description
DESCRSHORT
VARCHAR2(30)
Business unit short description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
122
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema COMPANY
COMPANY Dimension detailing Company attributes Column name
Key
Data type
Column description
COMPANY_KEY
PK
NUMBER
Surrogate key to COMPANY
COMPANY
VARCHAR2(9)
Company code
EFFDT
DATETIME
Effective from date
EFF_STATUS
VARCHAR2(3)
Effective status (Active or Inactive)
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
TO_EFFDT
DATE
Effective to date
DESCR
VARCHAR2(90) Company description
DESCRSHORT
VARCHAR2(30) Company short description
COUNTRY
VARCHAR2(9)
GEO_CODE
VARCHAR2(33) Geographical code
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
Country
CURRENCY Dimension with Currency attributes Column name
Key
CURRENCY_KEY
PK
Data type
Column description
NUMBER
Surrogate key to CURRENCY
CURRENCY
VARCHAR2(9)
Currency code
EFFDT
DATETIME
Effective from date
TO_EFFDT
DATETIME
Effective to date
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
EFF_STATUS
VARCHAR2(3)
Effective status (Active or Inactive)
DESCR
VARCHAR2(90) Currency long description
DESCRSHORT
VARCHAR2(30) Currency short description
COUNTRY
VARCHAR2(9)
CUR_SYMBOL
VARCHAR2(12) Currency symbol
Country code
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
123
A
A
Rapid Mart Data Schema DEPARTMENT
Column name
Key
Data type
Column description
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
DEPARTMENT Dimension with Department attributes Column name
Key
Data type
Column description
DEPARTMENT_KEY
PK
NUMBER
Surrogate key to DEPARTMENT
SETID
VARCHAR2(15)
SetID is used to differentiate between Department values that belong to different Business Units
DEPTID
VARCHAR2(30)
Department code
EFFDT
DATETIME
Effective from date
TO_EFFDT
DATETIME
Effective to date
EFF_STATUS
VARCHAR2(1)
Effective status (Active or Inactive)
DESCR
VARCHAR2(90)
Department long description
DESCRSHORT
VARCHAR2(30)
Department short description
COMPANY_KEY
NUMBER
Surrogate key to COMPANY
LOCATION
VARCHAR2(30)
Departments location
LOCATION_KEY
NUMBER
Surrogate key to LOCATION (-1 in this rapid mart. Used by PSFT HR rapid mart)
MANAGER_POSN
VARCHAR2(24)
Manager Position
MANAGER_NAME
VARCHAR2(90)
Manager Name
BUDGET_DEPTID
VARCHAR2(30)
Controlling budget department
MANAGER_POSITION_K EY
NUMBER
Surrogate key to EMPLOYEE. (-1 in this rapid mart. Used by PSFT HR rapid mart)
MANAGER_EMPLOYEE_ KEY
NUMBER
Surrogate key to EMPLOYEE. (-1 in this rapid mart. Used by PSFT HR rapid mart)
124
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema DEPARTMENT_HIER_HZ
Column name
Key
Data type
Column description
BUDGET_DEPARTMENT _KEY
NUMBER
Surrogate key to DEPARTMENT to identify budget department
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
DEPARTMENT_HIER_HZ Natural Department key horizontally flattened hierarchy. Column name
Key
Data type
Column description
SETID
PK
VARCHAR2(10) SetID is used to differentiate between Department values that belong to different Business Units
DEPTID
PK
VARCHAR2(30) Current leaf Department value
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1)
Effective date current date indicator
LEAF_LEVEL
NUMBER
Leaf level in horizontal flattening
MAXDEPTH
NUMBER
Maximum depth of hierarchy
LEVEL0_DEPTID
VARCHAR2(30) Level0 (root) value for the current leaf of Department
LEVEL0_DEPARTMENT_ KEY
NUMBER
LEVEL0_DESCRIPTION
VARCHAR2(90) Level0 (root) description for the current leaf of Department
LEVEL1_DEPTID
VARCHAR2(30) Level1 value for the current leaf of Department
LEVEL1_DEPARTMENT_ KEY
NUMBER
LEVEL1_DESCRIPTION
VARCHAR2(90) Level1 description for the current leaf of Department
LEVEL2_DEPTID
VARCHAR2(30) Level2 value for the current leaf of Department
Level0 (root) surrogate key for the current leaf of Department
Level1 surrogate key for the current leaf of Department
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
125
A
A
Rapid Mart Data Schema DEPARTMENT_HIER_HZ
Column name
Data type
Column description
LEVEL2_DEPARTMENT_ KEY
NUMBER
Level2 surrogate key for the current leaf of Department
LEVEL2_DESCRIPTION
VARCHAR2(90) Level2 description for the current leaf of Department
LEVEL3_DEPTID
VARCHAR2(30) Level3 value for the current leaf of Department
LEVEL3_DEPARTMENT_ KEY
NUMBER
LEVEL3_DESCRIPTION
VARCHAR2(90) Level3 description for the current leaf of Department
LEVEL4_DEPTID
VARCHAR2(30) Level4 value for the current leaf of Department
LEVEL4_DEPARTMENT_ KEY
NUMBER
LEVEL4_DESCRIPTION
VARCHAR2(90) Level4 description for the current leaf of Department
LEVEL5_DEPTID
VARCHAR2(30) Level5 value for the current leaf of Department
LEVEL5_DEPARTMENT_ KEY
NUMBER
LEVEL5_DESCRIPTION
VARCHAR2(90) Level5 description for the current leaf of Department
LEVEL6_DEPTID
VARCHAR2(30) Level6 value for the current leaf of Department
LEVEL6_DEPARTMENT_ KEY
NUMBER
LEVEL6_DESCRIPTION
VARCHAR2(90) Level6 description for the current leaf of Department
LEVEL7_DEPTID
VARCHAR2(30) Level7 value for the current leaf of Department
LEVEL7_DEPARTMENT_ KEY
NUMBER
LEVEL7_DESCRIPTION
VARCHAR2(90) Level7 description for the current leaf of Department
LEVEL8_DEPTID
VARCHAR2(30) Level8 value for the current leaf of Department
126
Key
Level3 surrogate key for the current leaf of Department
Level4 surrogate key for the current leaf of Department
Level5 surrogate key for the current leaf of Department
Level6 surrogate key for the current leaf of Department
Level7 surrogate key for the current leaf of Department
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema DEPARTMENT_HIER_PC
Column name
Key
Data type
Column description
LEVEL8_DEPARTMENT_ KEY
NUMBER
Level8 surrogate key for the current leaf of Department
LEVEL8_DESCRIPTION
VARCHAR2(90) Level8 description for the current leaf of Department
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
DEPARTMENT_HIER_PC Natural Department key parent/child relationship hierarchy Column name
Key Data type
DEPARTMENT_KEY
NUMBER
Column description Surrogate key to child Department
SETID
PK
VARCHAR2(1 SetID is used to differentiate between 5) Department values that belong to different Business Units
DEPTID
PK
VARCHAR2(3 Child Department 0)
DESCRIPTION EFFECTIVE_FM_DT
VARCHAR2(9 Child Department description 0) PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1 Effective date current date indicator )
PARENT_DEPARTMENT_KE Y
NUMBER
PARENT_DEPTID
VARCHAR2(3 Parent Department 0)
PARENT_DESCRIPTION
VARCHAR2(9 Parent Department description 0)
LOAD_DATE
DATETIME
LOAD_TIME
VARCHAR2(8 Time when the record was loaded into ) the target system
Parent Department surrogate key
Date and Time when the record was loaded into target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
127
A
A
Rapid Mart Data Schema DEPARTMENT_HIER_VR
DEPARTMENT_HIER_VR Natural Department key vertically flattened hierarchy Column name
Key Data type
SETID
PK
VARCHAR2(1 SetID is used to differentiate between 5) Department values that belong to different Business Units
DEPTID
PK
VARCHAR2(3 Ancestor Department code 0)
DESCENDENT_DEPTID
PK
VARCHAR2(3 Descendent Department code 0)
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1 Effective date current date indicator )
MAXDEPTH
NUMBER
Maximum depth of hierarchy
DEPTH
NUMBER
Depth of the descendent
ROOT_FLAG
NUMBER
Root flag (1 means this is a root)
LEAF_FLAG
NUMBER
Leaf flag (1 means this is a leaf)
TREELEVEL
NUMBER
Tree level
DEPARTMENT_KEY
NUMBER
Ancestor Department surrogate key
DEPARTMENT
VARCHAR2(9 Ancestor Department description 0)
DESCENDENT_DEPARTMEN T_KEY
NUMBER
DESCENDENT_DEPARTMEN T
VARCHAR2(9 Descendent Department description 0)
LOAD_DATE
DATETIME
LOAD_TIME
VARCHAR2(8 Time when the record was loaded into ) the target system
128
Column description
Descendent Department surrogate key
Date and Time when the record was loaded into target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema DISTRIB_LINE_FACT
DISTRIB_LINE_FACT Voucher distribution line transaction data Column name
Key
Data type
Column description
BUSINESS_UNIT_KEY
NUMBER
Surrogate key to the BUSINESS_UNIT dimension
VOUCHER_ID
VARCHAR2(24)
Voucher ID
VOUCHER_LINE_NUM
INTEGER
Voucher line number
DISTRIB_LINE_NUM
INTEGER
Distribution line number
VENDOR_KEY
NUMBER
Surrogate key to the VENDOR dimension
VENDOR_TYPE_KEY
NUMBER
Surrogate key to the VENDOR_TYPE dimension
VENDOR_ADDRESS_KEY
NUMBER
Surrogate key to the VENDOR_ADDRESS dimension
INVOICE_DT
DATE
Invoice date
DUE_DT
DATE
Due date
DSCNT_DUE_DT
DATE
Discount due date
INV_RECPT_DT
DATE
Invoice receipt date
RECEIPT_DT
DATE
Received date
SHIP DATE
DATE
Ship date
LAST_UPDATE_DT
DATE
Last updated date
TERM_BASIS_DT
DATE
Payment terms basis date
PAY_TRM_BSE_DT_OPT_ DESCR
VARCHAR2(30)
Payment terms basis date type description
PAYMENT_TERMS_KEY
NUMBER
Surrogate key to the PAYMENT_TERMS dimension
INVOICE_ITEM_KEY
NUMBER
Surrogate key to the INVOICE_ITEM S dimension
UNIT_OF_MEASURE_KEY
NUMBER
Surrogate key to the UNIT_OF_MEASURES dimension
BUSINESS_UNIT_GL_KEY
NUMBER
Surrogate key to the BUSINESS_UNIT dimension
ACCOUNT_KEY
NUMBER
Surrogate key to the ACCOUNT dimension
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
129
A
A
Rapid Mart Data Schema DISTRIB_LINE_FACT
Column name
Key
Data type
Column description
STATISTICS_CODE
VARCHAR2(9)
Statistics code
FOREIGN_AMOUNT
NUMBER(26,3)
Foreign amount
TXN_CURRENCY_KEY
NUMBER
Transaction currency surrogate key to the CURRENCY dimension
MONETARY_AMOUNT
NUMBER(26,3)
Monetary amount
DSCNT_AMT_GL
NUMBER(26,3)
GL discount amount
FREIGHT_AMT_GL
NUMBER(26,3)
GL freight amount
MEMO_AMT_GL
NUMBER(26,3)
GL memo amount
MISC_AMT_GL
NUMBER(26,3)
GL miscellaneous amount
SALETX_AMT_GL
NUMBER(26,3)
GL sales tax amount
USETAX_AMT_GL
NUMBER(26,3)
GL tax amount
STATISTIC_AMOUNT
NUMBER(15,2)
Statistic amount
QTY_VCHR
NUMBER(15,4)
Quantity vouchered
OPEN_ITEM_STATUS_DE SCR
VARCHAR2(30)
Open item status description
DESCR
VARCHAR2(90)
Description
MERCHANDISE_AMT
NUMBER(26,3)
Merchandise amount
ACCOUNTING_DT
DATE
Accounting date
BUSINESS_UNIT_PO_KEY
NUMBER
PO business unit surrogate key to the BUSINESS_UNIT dimension
PO_ID
VARCHAR2(30)
Purchase order
CURRENCY_KEY
NUMBER
Surrogate key to the CURRENCY dimension
COST_TYPE
VARCHAR2(3)
Cost type
FREIGHT_AMT
NUMBER(26,3)
Freight amount
SALETX_AMT
NUMBER(26,3)
Sales tax amount
USETAX_AMT
NUMBER(26,3)
Tax amount
VAT_INV_AMT
NUMBER(26,3)
VAT invoice amount
VAT_NOINV_AMT
NUMBER(26,3)
VAT non-invoice amount
BUSINESS_UNIT_RECV_K EY
NUMBER
Surrogate key to the BUSINESS_UNIT dimension
RECEIVER_ID
VARCHAR2(30)
Receipt number
DSCNT_AMT
NUMBER(26,3)
Discount amount
130
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema DISTRIB_LINE_FACT
Column name
Key
Data type
Column description
OPERATING_UNIT_KEY
NUMBER
Surrogate key to the OPERATING_UNIT dimension
DEPARTMENT_KEY
NUMBER
Surrogate key to the DEPARTMENT dimension
ALTACCOUNT_KEY
NUMBER
Surrogate key to the ALTACCOUNT dimension
WITHHOLDING_KEY
NUMBER
Surrogate key to the WITHHOLDING dimension
BASE_CURRENCY_KEY
NUMBER
Base currency surrogate key to the CURRENCY dimension
RT_TYPE
VARCHAR2(15)
Rate type
RATE_MULT
NUMBER(15,8)
Rate multiplier
RATE_DIV
NUMBER(15,8)
Rate divisor
MONETARY_AMT_BSE
NUMBER(26,3)
Base monetary amount
MERCH_AMT_BSE
NUMBER(26,3)
Base merchandise amount
MERCH_AMT_GL
NUMBER(26,3)
Amount in GL business unit currency
DSCNT_AMT_BSE
NUMBER(26,3)
Base discount amount
SALETX_AMT_BSE
NUMBER(26,3)
Base sales tax amount
USETAX_AMT_BSE
NUMBER(26,3)
Base tax amount
FREIGHT_AMT_BSE
NUMBER(26,3)
Base freight amount
CURRENCY_RPTG_KEY
NUMBER
Reporting currency surrogate key to the CURRENCY dimension
TIME_KEY
NUMBER
Surrogate key to TIME_DIMENSION
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
131
A
A
Rapid Mart Data Schema FISCAL_CALENDAR
FISCAL_CALENDAR Time dimension built with the monthly granularity of fiscal months Column name
Key
Data type
Column description
FISCAL_YEAR
PK
NUMBER
Fiscal year. Used to link to FISCAL_YEAR of the fact tables.
ACCOUNTING_PERIOD PK
NUMBER
Accounting period. Used to link to ACCOUNTING_PERIOD of the fact tables.
PERIOD_NAME
VARCHAR2(30) Period description
PERIOD_ABBRV
VARCHAR2(30) Abbreviated period description
CURRENT_FISCAL_YE AR
VARCHAR2(30) Current fiscal year indicator (Y or N)
CURRENT_ACCOUNTI NG_PERIOD
VARCHAR2(30) Current accounting period indicator (Y or N)
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
INVOICE_ITEM Dimension with Invoice item attributes Column name
Key
Data type
Column description
INVOICE_ITEM_KEY
PK
NUMBER
Surrogate key to INVOICE_ITEM
SETID
VARCHAR2(15)
SetID is used to differentiate between item values that belong to different Business Units
INV_ITEM_ID
VARCHAR2(54)
Item ID
EFFDT
DATE
Effective from date
TO_EFFDT
DATE
Effective to date
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
INV_ITEM_TEMPLATE
VARCHAR2(15)
Item template ID
INV_ITEM_TYPE
VARCHAR2(15)
Item type
LAST_DTTM_UPDATE
DATE
Last change date
132
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema LOOKUP_INDEXES
Column name
Key
Data type
Column description
DESCR254
VARCHAR2(762) Item long description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
LOOKUP_INDEXES Rapid Mart auxiliary table to store information about target table indexes. The table is used by Preprocessing and Postprocessing fact table stored procedures (optional) Column name
Key
Data type
Column description
INDEX_NAME
PK
VARCHAR2(64) Index unique name
TABLE_NAME
VARCHAR2(64) Table unique aname
COLUMN_NAME1
VARCHAR2(64) First column name which is a member of the index
COLUMN_NAME2
VARCHAR2(64) Second column name which is a member of the index
COLUMN_NAME3
VARCHAR2(64) Third column name which is a member of the index
ADDITIONAL_TEXT
VARCHAR2(64) Tablespace and PARALLEL type
MONTH_DIMENSION Dimension that works in conjuction with TIME_DIMENSION to provide monthly granualarity of fact table data Column name
Key
Data type
Column description
MONTH_KEY
PK
NUMBER
Smart key in format ‘YYYYMM’. Used to link to MONTH_KEY in TIME_DIMENSION
MONTH
NUMBER
Calendar month number
QUARTER
NUMBER
Calendar quarter number
YEAR
NUMBER
Year
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
133
A
A
Rapid Mart Data Schema OPEN_LIAB_FACT
Column name
Key
Data type
Column description
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
OPEN_LIAB_FACT Open liabilities transaction data Column name
Key
Data type
Column description
BUSINESS_UNIT_GL_K EY
NUMBER
GL business unit surrogate key to the BUSINESS_UNIT dimension
BASE_CURRENCY_KE Y
NUMBER
Base currency surrogate key to the CURRENCY dimension
BUSINESS_UNIT_KEY
NUMBER
Surrogate key to the BUSINESS_UNIT dimension
VOUCHER_ID
VARCHAR(24)
Voucher ID
PYMNT_CNT
INTEGER
Number of payments
VOUCHER_LINE_NUM
INTEGER
Voucher line number
AS_OF_DATE
DATE
As of date
VENDOR_KEY
NUMBER
Surrogate key to the VENDOR dimension
INVOICE_DT
DATE
Invoice date
INVOICE_ID
VARCHAR2(66)
Invoice number
PAYMENT_TERMS_KE Y
NUMBER
Surrogate key to the PAYMENT_TERMS dimension
ACCOUNTING_DT
DATE
Accounting date
ENTRY_STATUS_DESC R
VARCHAR2(30)
Entry status description
PYMNT_HOLD
VARCHAR2(3)
Payment hold indicator
PYMNT_HOLD_WTHD
VARCHAR2(3)
Payment on witholding hold
UNREC_LIAB_IND
VARCHAR2(3)
Unrecorded liability indicator
AGE_LIAB_IND
VARCHAR2(3)
Age liability indicator
SCHEDULED_PAY_DT
DATE
Scheduled to pay date
134
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema OPERATING_UNIT
Column name
Key
Data type
Column description
AP_AGING_KEY
NUMBER
Surrogate key to the AP_AGING dimension
APAGING_AGE_BUCKE T
INTEGER
Aging category
GROSS_AMT_BSE
NUMBER(26,3)
Base gross amount
DSCNT_AMT_BSE
NUMBER(26,3)
base discount amount
DESCR_LN1
VARCHAR2(150)
Reason why record has been assigned as an open liability
CURRENCY_KEY
NUMBER
Surrogate key to the CURRENCY dimension
CUR_RT_TYPE
VARCHAR2(15)
Exchange rate type
CUR_EFFDT
DATE
Currency effective date
RATE_MULT
NUMBER(15,8)
Rate multiplier
RATE_DIV
NUMBER(15,8)
Rate divisor
TIME_KEY
NUMBER
Surrogate key to TIME_DIMENSION
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
OPERATING_UNIT Dimension with Operating Unit attributes Column name
Key
Data type
Column description
OPERATING_UNIT_KE Y
PK
NUMBER(15)
Surrogate key to OPERATING_UNIT
SETID
VARCHAR2(15)
SetID is used to differentiate between Operating Unit values that belong to different Business Units
OPERATING_UNIT
VARCHAR2(24)
Operating unit code
EFFDT
DATETIME
Effective from date
EFF_STATUS
VARCHAR2(1)
Effective status (Active or Inactive)
DESCR
VARCHAR2(90)
Operating unit long description
DESCRSHORT
VARCHAR2(30)
Operating unit short description
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
135
A
A
Rapid Mart Data Schema OPERATING_UNIT_HIER_HZ
Column name
Key
Data type
Column description
DATETIME
Effective to date
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
TO_EFFDT
OPERATING_UNIT_HIER_HZ Natural Operating Unit key horizontally flattened hierarchy. Column name
Key
Data type
SETID
PK
VARCHAR2(10) SetID is used to differentiate between Operating Unit values that belong to different Business Units
OPERUNIT
PK
VARCHAR2(30) Current leaf Operating Unit value
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1)
Effective date current date indicator
LEAF_LEVEL
NUMBER
Leaf level in horizontal flattening
MAXDEPTH
NUMBER
Maximum depth of hierarchy
LEVEL0_OPERUNIT
VARCHAR2(30) Level0 (root) value for the current leaf of Operating Unit
LEVEL0_OPERUNIT_K EY
NUMBER
LEVEL0_DESCRIPTION
VARCHAR2(90) Level0 (root) description for the current leaf of Operating Unit
LEVEL1_OPERUNIT
VARCHAR2(30) Level1 value for the current leaf of Operating Unit
LEVEL1_OPERUNIT_K EY
NUMBER
LEVEL1_DESCRIPTION
VARCHAR2(90) Level1 description for the current leaf of Operating Unit
LEVEL2_OPERUNIT
VARCHAR2(30) Level2 value for the current leaf of Operating Unit
136
Column description
Level0 (root) surrogate key for the current leaf of Operating Unit
Level1 surrogate key for the current leaf of Operating Unit
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema OPERATING_UNIT_HIER_HZ
Column name
Key
Data type
Column description
LEVEL2_OPERUNIT_K EY
NUMBER
Level2 surrogate key for the current leaf of Operating Unit
LEVEL2_DESCRIPTION
VARCHAR2(90) Level2 description for the current leaf of Operating Unit
LEVEL3_OPERUNIT
VARCHAR2(30) Level3 value for the current leaf of Operating Unit
LEVEL3_OPERUNIT_K EY
NUMBER
LEVEL3_DESCRIPTION
VARCHAR2(90) Level3 description for the current leaf of Operating Unit
LEVEL4_OPERUNIT
VARCHAR2(30) Level4 value for the current leaf of Operating Unit
LEVEL4_OPERUNIT_K EY
NUMBER
LEVEL4_DESCRIPTION
VARCHAR2(90) Level4 description for the current leaf of Operating Unit
LEVEL5_OPERUNIT
VARCHAR2(30) Level5 value for the current leaf of Operating Unit
LEVEL5_OPERUNIT_K EY
NUMBER
LEVEL5_DESCRIPTION
VARCHAR2(90) Level5 description for the current leaf of Operating Unit
LEVEL6_OPERUNIT
VARCHAR2(30) Level6 value for the current leaf of Operating Unit
LEVEL6_OPERUNIT_K EY
NUMBER
LEVEL6_DESCRIPTION
VARCHAR2(90) Level6 description for the current leaf of Operating Unit
LEVEL7_OPERUNIT
VARCHAR2(30) Level7 value for the current leaf of Operating Unit
LEVEL7_OPERUNIT_K EY
NUMBER
LEVEL7_DESCRIPTION
VARCHAR2(90) Level7 description for the current leaf of Operating Unit
LEVEL8_OPERUNIT
VARCHAR2(30) Level8 value for the current leaf of Operating Unit
Level3 surrogate key for the current leaf of Operating Unit
Level4 surrogate key for the current leaf of Operating Unit
Level5 surrogate key for the current leaf of Operating Unit
Level6 surrogate key for the current leaf of Operating Unit
Level7 surrogate key for the current leaf of Operating Unit
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
137
A
A
Rapid Mart Data Schema OPERATING_UNIT_HIER_PC
Column name
Key
Data type
Column description
LEVEL8_OPERUNIT_K EY
NUMBER
Level8 surrogate key for the current leaf of Operating Unit
LEVEL8_DESCRIPTION
VARCHAR2(90) Level8 description for the current leaf of Operating Unit
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
OPERATING_UNIT_HIER_PC Natural Operating Unit key parent/child relationship hierarchy Column name
Key Data type
OPER_UNIT_KEY
NUMBER
Column description Surrogate key to child Operating Unit
SETID
PK
VARCHAR2(1 SetID is used to differentiate between 5) Operating Unit values that belong to different Business Units
OPER_UNIT
PK
VARCHAR2(3 Child Operating Unit 0)
DESCRIPTION
VARCHAR2(9 Child Operating Unit description 0)
EFFECTIVE_FM_DT
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1 Effective date current date indicator )
PARENT_OPER_UNIT_KEY
NUMBER
PARENT_OPER_UNIT
VARCHAR2(3 Parent Operating Unit 0)
PARENT_DESCRIPTION
VARCHAR2(9 Parent Operating Unit description 0)
LOAD_DATE
DATETIME
LOAD_TIME
VARCHAR2(8 Time when the record was loaded into ) the target system
138
PK
Parent Operating Unit surrogate key
Date and Time when the record was loaded into target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema OPERATING_UNIT_HIER_VR
OPERATING_UNIT_HIER_VR Natural Operating Unit key vertically flattened hierarchy Column name
Key Data type
Column description
SETID
PK
VARCHAR2(1 SetID is used to differentiate between 5) Operating Unit values that belong to different Business Units
ANCESTOR_OPERUNIT
PK
VARCHAR2(3 Ancestor Operating Unit code 0)
DESCENDENT_OPERUNIT
PK
VARCHAR2(3 Descendent Operating Unit code 0)
EFFECTIVE_FM_DT
PK
DATE
Effective from date
EFFECTIVE_TO_DT
DATE
Effective to date
CURRENT_STATUS
VARCHAR2(1 Effective date current date indicator )
MAXDEPTH
NUMBER
Maximum depth of hierarchy
DEPTH
NUMBER
Depth of the descendent
ROOT_FLAG
NUMBER
Root flag (1 means this is a root)
LEAF_FLAG
NUMBER
Leaf flag (1 means this is a leaf)
TREELEVEL
NUMBER
Tree level
ANCESTOR_OPERUNIT_KE Y
NUMBER
Ancestor Operating Unit surrogate key
ANCESTOR_OPERUNIT_DE SCR
VARCHAR2(9 Ancestor Operating Unit description 0)
DESCENDENT_OPERUNIT_ KEY
NUMBER
DESCENDENT_OPERUNIT_ DESCR
VARCHAR2(9 Descendent Operating Unit 0) description
LOAD_DATE
DATETIME
LOAD_TIME
VARCHAR2(8 Time when the record was loaded into ) the target system
Descendent Operating Unit surrogate key
Date and Time when the record was loaded into target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
139
A
A
Rapid Mart Data Schema PS_AP_SET_CNTRL_REC
PS_AP_SET_CNTRL_REC Local copy of the PeopleSoft source control record. This table holds the relationships between transaction table BUSINESS_UNIT codes and control table SETID’s. Column name
Key
Data type
SETCNTRLVALUE
PK
VARCHAR2(20) Set control value holding BUSINESS_UNIT references
REC_GROUP_ID
PK
VARCHAR2(10) Record group ID
RECNAME
PK
VARCHAR2(15) Control table name. Identifies the Accounts Receivables control table from which the SETID is associated to BUSINESS_UNIT
SETID
Column description
VARCHAR2(5)
Set ID
STAGE_AP_PAYMENT_FACT AP_PAYMENT_FACT staging table that promotes loading efficiency by separating source database data extraction from the target database data transformation procedure Column name
Key
Data type
Column description
BANK_SETID
VARCHAR2(15)
Bank setID
BANK_CD
VARCHAR2(15)
Bank code
BANK_ACCT_KEY
VARCHAR2(12)
Bank account
PYMNT_ID
VARCHAR2(30)
Payment number
PYMNT_ID_REF
VARCHAR2(60)
Payment reference
REMIT_SETID
VARCHAR2(15)
Remit vendor setID
REMIT_VENDOR
VARCHAR2(30)
Remit vendor
VNDR_LOC
VARCHAR2(30)
Remit vendor address
EMPLID
VARCHAR2(33)
Employee ID
NAME1
VARCHAR2(120) Vendor name
NAME2
VARCHAR2(120) Vendor name additional description
COUNTRY
VARCHAR2(9)
ADDRESS1
VARCHAR2(165) Address line 1
140
Country
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema STAGE_AP_PAYMENT_FACT
Column name
Key
Data type
Column description
ADDRESS2
VARCHAR2(165) Address line 2
ADDRESS3
VARCHAR2(165) Address line 3
ADDRESS4
VARCHAR2(165) Address line 4
CITY
VARCHAR2(90)
City
HOUSE_TYPE
VARCHAR2(6)
House type
ADDR_FIELD1
VARCHAR2(6)
Address field 1
ADDR_FIELD2
VARCHAR2(12)
Address field 2
ADDR_FIELD3
VARCHAR2(12)
Address field 3
COUNTY
VARCHAR2(90)
County
STATE
VARCHAR2(18)
State
POSTAL
VARCHAR2(36)
Postal code
GEO_CODE
VARCHAR2(33)
Tax vendor geographical code
PYMNT_DT
DATE
Payment date
CREATION_DT
DATE
Creation date
OPRID
VARCHAR2(90)
User ID
PYMNT_AMT
NUMBER(26,3)
Payment amount
CURRENCY_PYMNT
VARCHAR2(9)
Payment currency
PYMNT_METHOD
VARCHAR2(30)
Payment method
PYMNT_STATUS
VARCHAR2(30)
Payment status
CANCEL_ACTION
VARCHAR2(30)
Cancel action
CANCEL_DT
DATE
Cancellation date
ACCOUNTING_DT
DATE
Accounting date
POST_STATUS_AP
VARCHAR2(30)
Post status
RECON_STATUS
VARCHAR2(30)
Recon status
PYMNT_TYPE
VARCHAR2(30)
Payment type
BUSINESS_UNIT_GL
VARCHAR2(9)
GL business unit
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
141
A
A
Rapid Mart Data Schema STAGE_CAL_PERIODS
STAGE_CAL_PERIODS Time dimension staging table to enable more efficient data extraction from the source calendar table Column name
Key
Data type
Column description
CALENDAR_ID
PK
VARCHAR2(2)
Calendar identifier
FISCAL_YEAR
PK
NUMBER
Fiscal year
ACCOUNTING_PERIOD PK
NUMBER
Accounting period
BEGIN_DT
DATE
Period start date
END_DT
DATE
Period end date
PERIOD_NAME
VARCHAR2(30) Period name
PERIOD_ABBRV
VARCHAR2(3)
PD_ALLOC
NUMBER(18,15) Period allocation
PD_ALLOC_TOTAL
NUMBER(18,15) Total number of allocation periods
ALLOC_UPDATE_SW
VARCHAR2(1)
allocation update switch
LOAD_DATE
DATE
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
Period abbreviation
STAGE_DISTRIB_LINE_FACT DISTRIB_LINE_FACT staging table that promotes loading efficiency by separating source database data extraction from the target database data transformation procedure Column name
Data type
Column description
BUSINESS_UNIT
VARCHAR2(15)
Business unit
VOUCHER_ID
VARCHAR2(24)
Voucher ID
VOUCHER_LINE_NUM
INTEGER
Voucher line number
DISTRIB_LINE_NUM
INTEGER
Distribution line number
VENDOR_SETID
VARCHAR2(15)
Vendor setID
VENDOR_ID
VARCHAR2(30)
Vendor ID
VNDR_LOC
VARCHAR2(30)
Vendor location
142
Key
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema STAGE_DISTRIB_LINE_FACT
Column name
Key
Data type
Column description
ADDRESS_SEQ_NUM
INTEGER
Vendor Address sequence number
INVOICE_DT
DATE
Invoice date
DUE_DT
DATE
Due date
DSCNT_DUE_DT
DATE
Discount due date
INV_RECPT_DT
DATE
Invoice receipt date
RECEIPT_DT
DATE
Received date
SHIP DATE
DATE
Ship date
LAST_UPDATE_DT
DATE
Last updated date
TERM_BASIS_DT
DATE
Payment terms basis date
PAY_TRM_BSE_DT_OPT
VARCHAR2(3)
Payment terms basis date type
PYMNT_TERMS_CD
VARCHAR2(15)
Payment terms code
ITM_SETID
VARCHAR2(15)
Item setID
INV_ITEM_ID
VARCHAR2(54)
Invoice item ID
UNIT_OF_MEASURE
VARCHAR2(9)
Unit of measure
BUSINESS_UNIT_GL
VARCHAR2(15)
GL Business unit
ACCOUNT
VARCHAR2(30)
Account
STATISTICS_CODE
VARCHAR2(9)
Statistics code
FOREIGN_AMOUNT
NUMBER(26,3)
Foreign amount
TXN_CURRENCY_CD
VARCHAR2(9)
Transaction currency
MONETARY_AMOUNT
NUMBER(26,3)
Monetary amount
DSCNT_AMT_GL
NUMBER(26,3)
GL discount amount
FREIGHT_AMT_GL
NUMBER(26,3)
GL freight amount
MEMO_AMT_GL
NUMBER(26,3)
GL memo amount
MISC_AMT_GL
NUMBER(26,3)
GL miscellaneous amount
SALETX_AMT_GL
NUMBER(26,3)
GL sales tax amount
USETAX_AMT_GL
NUMBER(26,3)
GL tax amount
STATISTIC_AMOUNT
NUMBER(15,2)
Statistic amount
QTY_VCHR
NUMBER(15,4)
Quantity vouchered
OPEN_ITEM_STATUS
VARCHAR2(3)
Open item status
DESCR
VARCHAR2(90)
Description
MERCHANDISE_AMT
NUMBER(26,3)
Merchandise amount
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
143
A
A
Rapid Mart Data Schema STAGE_DISTRIB_LINE_FACT
Column name
Key
Data type
Column description
ACCOUNTING_DT
DATE
Accounting date
BUSINESS_UNIT_PO
VARCHAR2(15)
PO business unit
PO_ID
VARCHAR2(30)
Purchase order
CURRENCY_CD
VARCHAR2(9)
Currency code
COST_TYPE
VARCHAR2(3)
Cost type
FREIGHT_AMT
NUMBER(26,3)
Freight amount
SALETX_AMT
NUMBER(26,3)
Sales tax amount
USETAX_AMT
NUMBER(26,3)
Tax amount
VAT_INV_AMT
NUMBER(26,3)
VAT invoice amount
VAT_NOINV_AMT
NUMBER(26,3)
VAT non-invoice amount
BUSINESS_UNIT_RECV
VARCHAR2(15)
Receiving business unit
RECEIVER_ID
VARCHAR2(30)
Receipt number
DSCNT_AMT
NUMBER(26,3)
Discount amount
OPERATING_UNIT
VARCHAR2(24)
Operating unit
DEPTID
VARCHAR2(30)
Department
ALTACCT
VARCHAR2(30)
Alternate account
WTHD_SETID
VARCHAR2(15)
Withholding setID
WTHD_CD
VARCHAR2(15)
Withholding code
BASE_CURRENCY
VARCHAR2(9)
Base currency
RT_TYPE
VARCHAR2(15)
Rate type
RATE_MULT
NUMBER(15,8)
Rate multiplier
RATE_DIV
NUMBER(15,8)
Rate divisor
MONETARY_AMT_BSE
NUMBER(26,3)
Base monetary amount
MERCH_AMT_BSE
NUMBER(26,3)
Base merchandise amount
MERCH_AMT_GL
NUMBER(26,3)
Amount in GL business unit currency
DSCNT_AMT_BSE
NUMBER(26,3)
Base discount amount
SALETX_AMT_BSE
NUMBER(26,3)
Base sales tax amount
USETAX_AMT_BSE
NUMBER(26,3)
Base tax amount
FREIGHT_AMT_BSE
NUMBER(26,3)
Base freight amount
CURRENCY_RPTG
VARCHAR2(9)
Reporting currency
144
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema STAGE_OPEN_LIAB_FACT
Column name
Key
Data type
Column description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
STAGE_OPEN_LIAB_FACT Open liabilities transaction data Column name
Key
Data type
Column description
BUSINESS_UNIT_GL
VARCHAR2(15)
GL business unit
BASE_CURRENCY
VARCHAR2(9)
Base currency
BUSINESS_UNIT
VARCHAR2(15)
Business unit
VOUCHER_ID
VARCHAR2(24)
Voucher ID
PYMNT_CNT
INTEGER
Number of payments
VOUCHER_LINE_NUM
INTEGER
Voucher line number
AS_OF_DATE
DATE
As of date
VENDOR_SETID
VARCHAR2(15)
Vendor setID
VENDOR_ID
VARCHAR2(30)
Vendor ID
INVOICE_DT
DATE
Invoice date
INVOICE_ID
VARCHAR2(66)
Invoice number
PYMNT_TERMS_CD
VARCHAR2(15)
Payment terms ID
ACCOUNTING_DT
DATE
Accounting date
ENTRY_STATUS
VARCHAR2(3)
Entry status
PYMNT_HOLD
VARCHAR2(3)
Payment hold indicator
PYMNT_HOLD_WTHD
VARCHAR2(3)
Payment on witholding hold
UNREC_LIAB_IND
VARCHAR2(3)
Unrecorded liability indicator
AGE_LIAB_IND
VARCHAR2(3)
Age liability indicator
SCHEDULED_PAY_DT
DATE
Scheduled to pay date
APAGING_AGE_BUCK ET
INTEGER
Aging category
GROSS_AMT_BSE
NUMBER(26,3)
Base gross amount
DSCNT_AMT_BSE
NUMBER(26,3)
base discount amount
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
145
A
A
Rapid Mart Data Schema STAGE_TIME_DIM
Column name
Key
Data type
Column description
DESCR_LN1
VARCHAR2(150) Reason why record has been assigned as an open liability
CURRENCY_CD
VARCHAR2(9)
Currency code
CUR_RT_TYPE
VARCHAR2(15)
Exchange rate type
CUR_EFFDT
DATE
Currency effective date
RATE_MULT
NUMBER(15,8)
Rate multiplier
RATE_DIV
NUMBER(15,8)
Rate divisor
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
STAGE_TIME_DIM TIME_DIMENSION staging table that promotes loading efficiency by separating source database data extraction from the target database data transformation procedure. Column name
Key
Data type
Column description
PK
NUMBER
Smart key in theformat ‘YYYYMMDD’. Used to link to TIME_KEY in the fact tables at a daily granularity.
MONTH_KEY
NUMBER
Smart key in the format ‘YYYYMM’. Used to link to MONTH_KEY in MONTH_DIMENSION at a monthly granularity
DAY
DATETIME
Daily dates ranging between user defined star and end date
MONTH
NUMBER(28,7)
Calendar month numeric representation
QUARTER
NUMBER(28,7)
Calendar quarter numeric representation
YEAR
NUMBER(28,7)
Year
DAY_IN_WEEK
NUMBER(28,7)
Numeric representation of the day in week
WEEK_IN_YEAR
NUMBER(28,7)
Numeric representation of the week in year
TIME_KEY
146
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema STAGE_VOUCHER_FACT
Column name
Key
Data type
Column description
IS_WEEKEND
VARCHAR2(1)
Numeric representation of weekday or weekend days
FISCAL_YEAR
NUMBER(28,7)
Fiscal year
ACCOUNTING_PERIO D
NUMBER(28,7)
Accounting period
PERIOD_NAME
VARCHAR2(30)
Period description
PERIOD_ABBRV
VARCHAR2(3)
Abbreviated period description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
STAGE_VOUCHER_FACT Voucher summary staging table that promotes loading efficiency by separating source database data extraction from the target database data transformation procedure Column name
Key
Data type
Column description
BUSINESS_UNIT
VARCHAR2(15)
Business unit
VOUCHER_ID
VARCHAR2(24)
Voucher ID
VOUCHER_STYLE
VARCHAR2(12)
Voucher style
INVOICE_ID
VARCHAR2(66)
Invoice number
INVOICE_DT
DATE
Invoice date
VENDOR_SETID
VARCHAR2(15)
Vendor setID
VENDOR_ID
VARCHAR2(30)
Vendor ID
VNDR_LOC
VARCHAR2(30)
Vendor location
ADDRESS_SEQ_NUM
INTEGER
Vendor address sequence number
OPRID
VARCHAR2(90)
User ID
VCHR_TTL_LINES
INTEGER
Lines entered
CLOSE_STATUS
VARCHAR2(3)
Close status
ENTRY_STATUS
VARCHAR2(3)
Entry status
ACCOUNTING_DT
DATE
Accounting date
POST_STATUS_AP
VARCHAR2(3)
Post status
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
147
A
A
Rapid Mart Data Schema STAGE_VOUCHER_FACT
Column name
Key
Data type
Column description
BUSINESS_UNIT_GL
VARCHAR2(15)
GL business unit
DUP_INVOICE_STATUS
VARCHAR2(3)
Duplicate invoice status
VOUCHER_ID_RELATED
VARCHAR2(24)
Related voucher
GROSS_AMT
NUMBER(26,3)
Gross invoice amount
GROSS_AMT_BSE
NUMBER(26,3)
Base gross amount
TXN_CURRENCY_CD
VARCHAR2(9)
Transaction currency code
RT_TYPE
VARCHAR2(15)
Rate type
RATE_MULT
NUMBER(15,8)
Rate multiplier
RATE_DIV
NUMBER(15,8)
Rate divisor
BASE_CURRENCY
VARCHAR2(9)
Base currency
DSCNT_AMT
NUMBER(26,3)
Discount amount
DSCNT_AMT_BSE
NUMBER(26,3)
Base discount amount
DSCNT_PRORATE_FLG
VARCHAR2(3)
Prorate discount
USETAX_AMT
NUMBER(26,3)
Tax amount
USETAX_AMT_BSE
NUMBER(26,3)
Base use tax amount
SALETX_AMT
NUMBER(26,3)
Sales tax amount
SALETX_AMT_BSE
NUMBER(26,3)
Base sales tax amount
SALETX_PRORATE_FLG
VARCHAR2(3)
Prorate sales tax
VAT_INV_AMT
NUMBER(26,3)
VAT invoice amount
VAT_INV_AMT_BSE
NUMBER(26,3)
Base currency VAT invoice amount
VAT_NOINV_AMT
NUMBER(26,3)
VAT non-invoice amount
VAT_NOINV_AMT_BSE
NUMBER(26,3)
Base currency VAT non-invoice amount
FREIGHT_AMT
NUMBER(26,3)
Freight amount
FREIGHT_AMT_BSE
NUMBER(26,3)
Base freight amount
MISC_AMT
NUMBER(26,3)
Miscellaneous amount
MISC_AMT_BSE
NUMBER(26,3)
Base miscellaneous amount
DUE_DT
DATE
Due date
DSCNT_DUE_DT
DATE
Discount due date
PYMNT_TERMS_CD
VARCHAR2(15)
Payment terms ID
LAST_UPDATE_DT
DATE
Last updated date
148
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema STAGE_VOUCHER_FACT
Column name
Key
Data type
Column description
VCHR_TEMPLATE_ID
VARCHAR2(30)
Voucher template ID
VCHR_TMPLTE_DESCR
VARCHAR2(90)
Voucher template description
POST_STATUS_WTHD
VARCHAR2(3)
Withholding post status
VAT_BASIS_AMT
NUMBER(26,3)
VAT basis amount
VAT_BASIS_AMT_BSE
NUMBER(26,3)
Base currency VAT basis amount
VAT_CALC_AMT
NUMBER(26,3)
VAT calculated amount
VAT_ENTRD_AMT
NUMBER(26,3)
Entered VAT amount
VAT_RCVRY_AMT
NUMBER(26,3)
VAT recovery amount
VAT_RCVRY_AMT_BSE
NUMBER(26,3)
Base currency VAT recovery amount
MERCHANDISE_AMT
NUMBER(26,3)
Merchandise amount
MERCH_AMT_BSE
NUMBER(26,3)
Base merchandise amount
MANUAL_CLOSE_DT
DATE
Manual close date
CUR_RT_SOURCE
VARCHAR2(30)
Exchange rate source
DSCNT_AMT_FLG
VARCHAR2(3)
Discount amount control
DUE_DT_FLG
VARCHAR2(3)
Due date control
VCHR_APPRVL_FLG
VARCHAR2(3)
Voucher approval flag
APPR_STATUS
VARCHAR2(3)
Approval status
COUNTRY_SHIP_TO
VARCHAR2(9)
Ship to country
SALETX_CALC_AMT
NUMBER(26,3)
Calculated sales tax amount
SALETX_CLC_AMT_BSE
NUMBER(26,3)
Base calculated sales tax amount
VOUCHER_TYPE
VARCHAR2(3)
Voucher type
COUNTRY_SHIP_FROM
VARCHAR2(9)
Ship from country
VAT_REBATE_AMT
NUMBER(26,3)
VAT rebate amount
VAT_REBATE_AMT_BSE
NUMBER(26,3)
Base currency VAT rebate amount
BUSINESS_UNIT_PO
VARCHAR2(15)
PO business unit
PO_ID
VARCHAR2(30)
Purchase order
INV_RECPT_DT
DATE
Invoice receipt date
RECEIPT_DT
DATE
Received date
SHIP DATE
DATE
Ship date
PAY_TRM_BSE_DT_OPT
VARCHAR2(3)
Payment terms basis date type
TERMS_BASIS_DT
DATE
Payment terms basis date
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
149
A
A
Rapid Mart Data Schema STAGE_VOUCHER_LINE_FACT
Column name
Key
Data type
Column description
VCHR_SRC
VARCHAR2(12)
Voucher source
PAY_AMT
NUMBER(26,3)
Payment amount
PAY_AMT_BSE
NUMBER(26,3)
Base currency payment amount
CURRENCY_RPTG
VARCHAR2(9)
Reporting currency
VAT_TRANS_AMT
NUMBER(26,3)
VAT transaction amount
VAT_TRANS_AMT_BSE
NUMBER(26,3)
Base currency VAT transaction amount
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
STAGE_VOUCHER_LINE_FACT Voucher line staging table that promotes loading efficiency by separating source database data extraction from the target database data transformation procedure Column name
Data type
Column description
BUSINESS_UNIT
VARCHAR2(15)
Business unit
VOUCHER_ID
VARCHAR2(24)
Voucher ID
VOUCHER_LINE_NUM
INTEGER
Voucher line number
TOTAL_DISTRIBS
INTEGER
Total number of distributions
VENDOR_SETID
VARCHAR2(15)
Vendor setID
VENDOR_ID
VARCHAR2(30)
Vendor ID
VNDR_LOC
VARCHAR2(30)
Vendor location
ADDRESS_SEQ_NUM
INTEGER
Vendor address sequence number
INVOICE_DT
DATE
Invoice date
ACCOUNTING_DT
DATE
Accounting date
DUE_DT
DATE
Due date
DSCNT_DUE_DT
DATE
Discount due date
INV_RECPT_DT
DATE
Invoice receipt date
RECEIPT_DT
DATE
Received date
150
Key
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema STAGE_VOUCHER_LINE_FACT
Column name
Key
Data type
Column description
SHIP DATE
DATE
Ship date
LAST_UPDATE_DT
DATE
Last updated date
TERMS_BASIS_DT
DATE
Payment terms basis date
PAY_TRM_BSE_DT_OPT
VARCHAR2(3)
Payment terms basis date type
PYMNT_TERMS_CD
VARCHAR2(15)
Payment terms ID
BUSINESS_UNIT_PO
VARCHAR2(15)
PO business unit
PO_ID
VARCHAR2(30)
Purchase order
LINE_NBR
INTEGER
Line number
SCHED_NBR
INTEGER
Schedule number
DESCR
VARCHAR2(90)
Description
MERCHANDISE_AMT
NUMBER(26,3)
Merchandise amount
MERCH_AMT_BSE
NUMBER(26,3)
Base merchandise amount
ITM_SETID
VARCHAR2(15)
Item setID
INV_ITEM_ID
VARCHAR2(54)
Item ID
QTY_VCHR
NUMBER(15,4)
Quantity vouchered
STATISTIC_AMOUNT
NUMBER(15,2)
Statistic amount
UNIT_OF_MEASURE
VARCHAR2(9)
Unit of measure
UNIT_PRICE
NUMBER(15,5)
Unit price
FRGHT_PRORATE_FLG
VARCHAR2(3)
Prorate freight charges
DSCNT_APPL_FLG
VARCHAR2(3)
Apply discount
BUSINESS_UNIT_RECV
VARCHAR2(15)
Receiving business unit
RECEIVER_ID
VARCHAR2(30)
Receipt number
RECV_LN_NBR
INTEGER
Receipt line
AMT_ONLY_FLG
VARCHAR2(3)
Amount only
TXN_CURRENCY_CD
VARCHAR2(9)
Transaction currency
BASE_CURRENCY
VARCHAR2(9)
Base currency
CURRENCY_CD
VARCHAR2(9)
Currency code
WTHD_SETID
VARCHAR2(15)
Withholding setID
WTHD_CD
VARCHAR2(15)
Withholding code
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
151
A
A
Rapid Mart Data Schema TEMP_AP_PAYMENT_DISCOUNTS
Column name
Key
Data type
Column description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
TEMP_AP_PAYMENT_DISCOUNTS Temporary table used when creating the AP_PAYMENT_DISCOUNTS dimension. The dimension attributes are extracted from the source table into the temporary table and then surrogate key lookup for PAYMENT_TERMS_KEY is provided before the results are passed to the target table. This table and AP_PAYMENT_DISCOUNTS are identical. Column name
Data type
Column description
AP_PAYMENT_DISCOUNT PK S_KEY
NUMBER
Surrogate key to AP_PAYMENT_DISCOUNTS
AP_PAYMENT_TERMS_KE Y
NUMBER
Surrogate key to AP_PAYMENT_TERMS
SETID
VARCHAR2(15)
SetID is used to differentiate between payment terms values that belong to different Business Units
PAYMNT_TERMS_CD
VARCHAR2(15)
Payment Terms ID
EFFDT
DATE
Effective from date
NET_TRMS_SEQ_NBR
INTEGER
Effective status (Active or Inactive)
DSCNT_TRMS_SEQ_NBR
INTEGER
Discount terms sequence number
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
TO_EFFDT
DATE
Effective to date
BASIS_FROM_DAY
INTEGER
Basis from date
BASIS_TO_DAY
INTEGER
Basis to date
DESCR
VARCHAR2(90)
Description
DESCRSHORT
VARCHAR2(30)
Short description
TMG_BASIS_OPTION_DE SCR
VARCHAR2(30)
Timing basis option description
TMG_REL_MONTH_VAL
INTEGER
End of relative month
TMG_MONTH_DUE_CD
VARCHAR2(6)
Month due
152
Key
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema TEMP_DEPARTMENT
Column name
Key
TMG_DAY_DUE_CD
Data type
Column description
VARCHAR2(6)
Day due
DUE_DT
DATE
Due date
TMG_DAY_INCR_VAL
INTEGER
Day increment
TMG_MONTH_INCR_VAL
INTEGER
Month increment
TMG_YEAR_INCR_VAL
INTEGER
Year increment
DSCNT_TRMS_TIME_ID
VARCHAR2(9)
Discount terms timing ID
TERMS_ADJUST_DAY
INTEGER
Terms adjustment days
DSCNT_TRMS_PERCENT
NUMBER(11,8)
Discount terms percent
DSCNT_TRMS_AMOUNT
NUMBER(16,3)
Discount terms amount
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
TEMP_DEPARTMENT Temporary table used when creating the Department dimension. First stage processing of the dimension extracts dimension attributes from the source table and surrogate key lookup for DEPARTMENT_KEY. The second stage process looks up these surrogate keys to provide further surrogate keys for BUDGET_DEPT Column name
Key
Data type
Column description
SETID
PK
VARCHAR2(15) SetID is used to differentiate between Department values that belong to different Business Units
DEPTID
PK
VARCHAR2(30) Department code
EFFDT
PK
DATETIME
Effective from date
BUDGET_DEPTID
VARCHAR2(30) Budget department code
TO_EFFDT
DATETIME
Effective to date
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
153
A
A
Rapid Mart Data Schema TEMP_VENDOR
TEMP_VENDOR Temporary table used when creating the Vendor dimension. The dimension attributes are extracted from the source table into the temporary table and then surrogate key lookup for REMIT_ADDRESS_KEY, PRIM_ADDRESS_KEY, ORDR_ADDRESS_KEY, CORPORATE_VENDOR_KEY and PRIMARY_VENDOR_KEY is provided before the results are passed to the target table. This table and VENDOR are identical Column name
Key
VENDOR_KEY
PK
Data type
Column description
NUMBER
Surrogate key to VENDOR
SETID
VARCHAR2(15) Vendor setID
VENDOR_ID
VARCHAR2(30) Vendor ID
VENDOR_NAME_SHOR T
VARCHAR2(42) Short vendor name
VNDR_NAME_SEQ_NU M
INTEGER
Vendor sequence number
NAME1
VARCHAR2(12 0)
Long vendor name
NAME2
VARCHAR2(12 0)
Long vendor name continuation
VENDOR_STATUS
VARCHAR2(3)
Vendor status
VENDOR_CLASS
VARCHAR2(3)
Vendor classification
VENDOR_PERSISTENC E
VARCHAR2(3)
Vendor persistence
REMIT_ADDR_SEQ_NU M
INTEGER
Remitting location
PRIM_ADDR_SEQ_NUM
INTEGER
Invoicing location
ADDR_SEQ_NUM_ORD R
INTEGER
Ordering location
REMIT_ADDRESS_KEY
NUMBER
Remitting location surrogate key
PRIM_ADDRESS_KEY
NUMBER
Invoicing location surrogate key
ORDR_ADDRESS_KEY
NUMBER
Ordering location surrogate key
REMIT_VENDOR_KEY
NUMBER
Remit vendor surrogate key
REMIT SETID
VARCHAR2(15) Remit vendor setID
REMIT_VENDOR
VARCHAR2(30) Remit vendor
154
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema TIME_DIMENSION
Column name
Key
Data type
Column description
CORPORATE_VENDOR_ KEY
NUMBER
Corporate vendor surrogate key
CORPORATE_SETID
VARCHAR2(15) Corporate vendor setID
CORPORATE_VENDOR
VARCHAR2(30) Corporate vendor
CUST_SETID
VARCHAR2(15) Customer setID
CUST_ID
VARCHAR2(45) Customer ID
WTHD_SW
VARCHAR2(3)
VNDR_STATUS_PO
VARCHAR2(3)
Open for ordering
PRIMARY_VENDOR_KE Y
NUMBER
Invoice vendor surrogate key
PRIMARY_VENDOR
VARCHAR2(30) Invoice vendor
LAST_ACTIVITY_DT
DATE
Last activity date
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
Withholding applicable
TIME_DIMENSION Time dimension with a daily granularity. Column name
Key
Data type
Column description
PK
NUMBER
Smart key in the format ‘YYYYMMDD’. Used to link to TIME_KEY in the fact tables at a daily granularity.
MONTH_KEY
NUMBER
Smart key in the format ‘YYYYMM’. Used to link to MONTH_KEY in MONTH_DIMENSION at a monthly granularity
DAY
DATETIME
Daily dates ranging between user defined star and end date
MONTH
NUMBER
Calendar month numeric representation
QUARTER
NUMBER
Calendar quarter numeric representation
YEAR
NUMBER
Year
TIME_KEY
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
155
A
A
Rapid Mart Data Schema TIME_DIMENSION
Column name
Key
Data type
Column description
DAY_IN_WEEK
NUMBER
Numeric representation of the day in week
WEEK_IN_YEAR
NUMBER
Numeric representation of the week in year
IS_WEEKEND
VARCHAR2(1)
Numeric representation of weekday or weekend days
FISCAL_YEAR
NUMBER
Fiscal year
ACCOUNTING_PERIOD
NUMBER
Accounting period
PERIOD_NAME
VARCHAR2(90)
Period description
PERIOD_ABBRV
VARCHAR2(9)
Abbreviated period description
CURRENT_FISCAL_YE AR
VARCHAR2(1)
Current fiscal year indicator (Y or N)
CURRENT_ACCOUNTI NG_PERIOD
VARCHAR2(1)
Current accounting period indicator (Y or N)
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
Dimension with Unit of Measure attributes Column name
Key
Data type
Column description
UNIT_OF_MEASURE_K PK EY
NUMBER
Surrogate key to UNIT_OF_MEASURE
UNIT_OF_MEASURE
VARCHAR2(9)
Unit of measure code
DESCR
VARCHAR2(90)
Unit of measure long description
DESCRSHORT
VARCHAR2(30)
Unit of measure short description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
156
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema UNIT_OF_MEASURE
UNIT_OF_MEASURE Dimension with Unit of Measure attributes Column name
Key
Data type
Column description
UNIT_OF_MEASURE_K PK EY
NUMBER
Surrogate key to UNIT_OF_MEASURE
UNIT_OF_MEASURE
VARCHAR2(9)
Unit of measure code
DESCR
VARCHAR2(90)
Unit of measure long description
DESCRSHORT
VARCHAR2(30)
Unit of measure short description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
VENDOR Dimension with Vendor attributes Column name
Key
Data type
Column description
VENDOR_KEY
PK
NUMBER
Surrogate key to VENDOR
SETID
VARCHAR2(15) Vendor setID
VENDOR_ID
VARCHAR2(30) Vendor ID
VENDOR_NAME_SHOR T
VARCHAR2(42) Short vendor name
VNDR_NAME_SEQ_NU M
INTEGER
NAME1
VARCHAR2(120 Long vendor name )
NAME2
VARCHAR2(120 Long vendor name continuation )
Vendor sequence number
VENDOR_STATUS
VARCHAR2(3)
Vendor status
VENDOR_CLASS
VARCHAR2(3)
Vendor classification
VENDOR_PERSISTENC E
VARCHAR2(3)
Vendor persistence
REMIT_ADDR_SEQ_NU M
INTEGER
Remitting location
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
157
A
A
Rapid Mart Data Schema VENDOR
Column name
Key
Data type
Column description
PRIM_ADDR_SEQ_NUM
INTEGER
Invoicing location
ADDR_SEQ_NUM_ORD R
INTEGER
Ordering location
REMIT_ADDRESS_KEY
NUMBER
Remitting location surrogate key
PRIM_ADDRESS_KEY
NUMBER
Invoicing location surrogate key
ORDR_ADDRESS_KEY
NUMBER
Ordering location surrogate key
REMIT_VENDOR_KEY
NUMBER
Remit vendor surrogate key
REMIT SETID
VARCHAR2(15) Remit vendor setID
REMIT_VENDOR
VARCHAR2(30) Remit vendor
CORPORATE_VENDOR_ KEY
NUMBER
CORPORATE_SETID
VARCHAR2(15) Corporate vendor setID
CORPORATE_VENDOR
VARCHAR2(30) Corporate vendor
CUST_SETID
VARCHAR2(15) Customer setID
CUST_ID
VARCHAR2(45) Customer ID
WTHD_SW
VARCHAR2(3)
Withholding applicable
VNDR_STATUS_PO
VARCHAR2(3)
Open for ordering
PRIMARY_VENDOR_KE Y
NUMBER
Invoice vendor surrogate key
PRIMARY_VENDOR
VARCHAR2(30) Invoice vendor
LAST_ACTIVITY_DT
DATE
Last activity date
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
158
Corporate vendor surrogate key
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema VENDOR_ADDRESS
VENDOR_ADDRESS Dimension with Vendor Address attributes Column name
Key
Data type
Column description
VENDOR_ADDRESS_KE PK Y
NUMBER
Surrogate key to VENDOR_ADDRESS
SETID
VARCHAR2(15) Vendor setID
VENDOR_ID
VARCHAR2(30) Vendor ID
ADDRESS_SEQ_NUM
INTEGER
Vendor address sequence number
EFFDT
DATE
Effective from date
EFF_STATUS
VARCHAR2(3)
Effective status (Active or Inactive)
TO_EFFDT
DATETIME
Effective to date
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
NAME1
VARCHAR2(120 Vendor Description )
EMAILID
VARCHAR2(210 Vendor Email ID )
COUNTRY
VARCHAR2(9)
ADDRESS1
VARCHAR2(165 Vendor Address line 1 )
ADDRESS2
VARCHAR2(165 Vendor Address line 2 )
ADDRESS3
VARCHAR2(165 Vendor Address line 3 )
ADDRESS4
VARCHAR2(165 Vendor Address line 4 )
CITY
VARCHAR2(90) City
COUNTY
VARCHAR2(90) County
STATE
VARCHAR2(18) State
POSTAL
VARCHAR2(36) Postal code
GEO_CODE
VARCHAR2(33) Tax vendor geographical code
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
Country
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
159
A
A
Rapid Mart Data Schema VENDOR_TYPE
VENDOR_TYPE Dimension with Vendor Type attributes Column name
Key
Data type
Column description
VENDOR_TYPE_KEY
PK
NUMBER
Surrogate key to VENDOR_TYPE
SETID
VARCHAR2(15) Vendor setID
VENDOR_ID
VARCHAR2(30) Vendor ID
VNDR_LOC
VARCHAR2(30) Vendor location
EFFDT
DATE
Effective from date
TO_EFFDT
DATETIME
Effective to date
CURRENT_IND
VARCHAR2(1)
Effective date current date indicator
VENDOR_TYPE
VARCHAR2(9)
Vendor type
DESCRSHORT
VARCHAR2(30) Short description
LOAD_DATE
DATETIME
Date and Time when the record was loaded into target system
LOAD_TIME
VARCHAR2(8)
Time when the record was loaded into the target system
VOUCHER_FACT Voucher summary transaction table Column name
Data type
Column description
BUSINESS_UNIT_KEY
NUMBER
Surrogate key to the BUSINESS_UNIT dimension
VOUCHER_ID
VARCHAR2(24)
Voucher ID
VOUCHER_STYLE_DESC R
VARCHAR2(30)
Voucher style description
INVOICE_ID
VARCHAR2(66)
Invoice number
INVOICE_DT
DATE
Invoice date
VENDOR_KEY
NUMBER
Surrogate key to the VENDOR dimension
VENDOR_TYPE_KEY
NUMBER
Surrogate key to the VENDOR_TYPE dimension
160
Key
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema VOUCHER_FACT
Column name
Key
Data type
Column description
VENDOR_ADDRESS_KEY
NUMBER
Surrogate key to the VENDOR_ADDRESS dimension
OPRID
VARCHAR2(90)
User ID
VCHR_TTL_LINES
INTEGER
Lines entered
CLOSE_STATUS_DESCR
VARCHAR2(30)
Close status description
ENTRY_STATUS_DESCR
VARCHAR2(30)
Entry status description
ACCOUNTING_DT
DATE
Accounting date
POST_STATUS_AP_DESC R
VARCHAR2(30)
Post status description
BUSINESS_UNIT_GL_KEY
NUMBER
GL business unit surrogate key to the BUSINESS_UNIT dimension
DUP_INVOICE_STATUS
VARCHAR2(3)
Duplicate invoice status
VOUCHER_ID_RELATED
VARCHAR2(24)
Related voucher
GROSS_AMT
NUMBER(26,3)
Gross invoice amount
GROSS_AMT_BSE
NUMBER(26,3)
Base gross amount
TXN_CURRENCY_KEY
NUMBER
Transaction currency surrogate key to the CURRENCY dimension
RT_TYPE
VARCHAR2(15)
Rate type
RATE_MULT
NUMBER(15,8)
Rate multiplier
RATE_DIV
NUMBER(15,8)
Rate divisor
BASE_CURRENCY_KEY
NUMBER
Base currency surrogate key to the CURRENCY dimension
DSCNT_AMT
NUMBER(26,3)
Discount amount
DSCNT_AMT_BSE
NUMBER(26,3)
Base discount amount
DSCNT_PRORATE_FLG
VARCHAR2(3)
Prorate discount
USETAX_AMT
NUMBER(26,3)
Tax amount
USETAX_AMT_BSE
NUMBER(26,3)
Base use tax amount
SALETX_AMT
NUMBER(26,3)
Sales tax amount
SALETX_AMT_BSE
NUMBER(26,3)
Base sales tax amount
SALETX_PRORATE_FLG
VARCHAR2(3)
Prorate sales tax
VAT_INV_AMT
NUMBER(26,3)
VAT invoice amount
VAT_INV_AMT_BSE
NUMBER(26,3)
Base currency VAT invoice amount
VAT_NOINV_AMT
NUMBER(26,3)
VAT non-invoice amount
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
161
A
A
Rapid Mart Data Schema VOUCHER_FACT
Column name
Key
Data type
Column description
VAT_NOINV_AMT_BSE
NUMBER(26,3)
Base currency VAT non-invoice amount
FREIGHT_AMT
NUMBER(26,3)
Freight amount
FREIGHT_AMT_BSE
NUMBER(26,3)
Base freight amount
MISC_AMT
NUMBER(26,3)
Miscellaneous amount
MISC_AMT_BSE
NUMBER(26,3)
Base miscellaneous amount
DUE_DT
DATE
Due date
DSCNT_DUE_DT
DATE
Discount due date
PAYMENT_TERMS_KEY
NUMBER
Surrogate key to the PAYMENT_TERMS dimension
LAST_UPDATE_DT
DATE
Last updated date
VCHR_TEMPLATE_ID
VARCHAR2(30)
Voucher template ID
VCHR_TMPLTE_DESCR
VARCHAR2(90)
Voucher template description
POST_STATUS_WTHD_D ESCR
VARCHAR2(30)
Withholding post status description
VAT_BASIS_AMT
NUMBER(26,3)
VAT basis amount
VAT_BASIS_AMT_BSE
NUMBER(26,3)
Base currency VAT basis amount
VAT_CALC_AMT
NUMBER(26,3)
VAT calculated amount
VAT_ENTRD_AMT
NUMBER(26,3)
Entered VAT amount
VAT_RCVRY_AMT
NUMBER(26,3)
VAT recovery amount
VAT_RCVRY_AMT_BSE
NUMBER(26,3)
Base currency VAT recovery amount
MERCHANDISE_AMT
NUMBER(26,3)
Merchandise amount
MERCH_AMT_BSE
NUMBER(26,3)
Base merchandise amount
MANUAL_CLOSE_DT
DATE
Manual close date
CUR_RT_SOURCE_DESC R
VARCHAR2(30)
Exchange rate source description
DSCNT_AMT_FLG_DESC R
VARCHAR2(30)
Discount amount control description
DUE_DT_FLG_DESCR
VARCHAR2(30)
Due date control description
VCHR_APPRVL_FLG_DES CR
VARCHAR2(30)
Voucher approval flag description
APPR_STATUS_DESCR
VARCHAR2(30)
Approval status description
COUNTRY_SHIP_TO
VARCHAR2(9)
Ship to country
162
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema VOUCHER_FACT
Column name
Key
Data type
Column description
SALETX_CALC_AMT
NUMBER(26,3)
Calculated sales tax amount
SALETX_CLC_AMT_BSE
NUMBER(26,3)
Base calculated sales tax amount
VOUCHER_TYPE_DESCR
VARCHAR2(30)
Voucher type description
COUNTRY_SHIP_FROM
VARCHAR2(9)
Ship from country
VAT_REBATE_AMT
NUMBER(26,3)
VAT rebate amount
VAT_REBATE_AMT_BSE
NUMBER(26,3)
Base currency VAT rebate amount
BUSINESS_UNIT_PO_KEY
NUMBER
PO business unit surrogate key to the BUSINESS_UNIT dimension
PO_ID
VARCHAR2(30)
Purchase order
INV_RECPT_DT
DATE
Invoice receipt date
RECEIPT_DT
DATE
Received date
SHIP DATE
DATE
Ship date
PAY_TRM_BSE_DT_OPT_ DESCR
VARCHAR2(30)
Payment terms basis date type description
TERMS_BASIS_DT
DATE
Payment terms basis date
VCHR_SRC_DESCR
VARCHAR2(30)
Voucher source description
PAY_AMT
NUMBER(26,3)
Payment amount
PAY_AMT_BSE
NUMBER(26,3)
Base currency payment amount
CURRENCY_RPTG_KEY
NUMBER
Reporting currency surrogate key to the CURRENCY dimension
VAT_TRANS_AMT
NUMBER(26,3)
VAT transaction amount
VAT_TRANS_AMT_BSE
NUMBER(26,3)
Base currency VAT transaction amount
NUM_OF_DAYS_OPEN
NUMBER
Number of days open
NUM_DAYS_PAST_DUE
NUMBER
Number of days past due date
TIME_KEY
NUMBER
Surrogate key to TIME_DIMENSION
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
163
A
A
Rapid Mart Data Schema VOUCHER_LINE_FACT
VOUCHER_LINE_FACT Voucher line transaction table Column name
Data type
Column description
BUSINESS_UNIT_KEY
NUMBER
Surrogate key to the BUSINESS_UNIT dimension
VOUCHER_ID
VARCHAR2(24)
Voucher ID
VOUCHER_LINE_NUM
INTEGER
Voucher line number
TOTAL_DISTRIBS
INTEGER
Total number of distributions
VENDOR_KEY
NUMBER
Surrogate key to the VENDOR dimension
VENDOR_TYPE_KEY
NUMBER
Surrogate key to the VENDOR_TYPE dimension
VENDOR_ADDRESS_KEY
NUMBER
Surrogate key to the VENDOR_ADDRESS dimension
INVOICE_DT
DATE
Invoice date
ACCOUNTING_DT
DATE
Accounting date
DUE_DT
DATE
Due date
DSCNT_DUE_DT
DATE
Discount due date
INV_RECPT_DT
DATE
Invoice receipt date
RECEIPT_DT
DATE
Received date
SHIP DATE
DATE
Ship date
LAST_UPDATE_DT
DATE
Last updated date
TERMS_BASIS_DT
DATE
Payment terms basis date
PAY_TRM_BSE_DT_OPT_ DESCR
VARCHAR2(30)
Payment terms basis date type description
PAYMENT_TERMS_KEY
NUMBER
Surrogate key to the PAYMENT_TERMS dimension
BUSINESS_UNIT_PO_KEY
NUMBER
PO business unit surrogate key to the BUSINESS_UNIT dimension
PO_ID
VARCHAR2(30)
Purchase order
LINE_NBR
INTEGER
Line number
SCHED_NBR
INTEGER
Schedule number
DESCR
VARCHAR2(90)
Description
164
Key
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
Rapid Mart Data Schema VOUCHER_LINE_FACT
Column name
Key
Data type
Column description
MERCHANDISE_AMT
NUMBER(26,3)
Merchandise amount
MERCH_AMT_BSE
NUMBER(26,3)
Base merchandise amount
INVOICE_ITEM_KEY
NUMBER
Surrogate key to the INVOICE_ITEM S dimension
QTY_VCHR
NUMBER(15,4)
Quantity vouchered
STATISTIC_AMOUNT
NUMBER(15,2)
Statistic amount
UNIT_OF_MEASURE_KEY
NUMBER
Surrogate key to the UNIT_OF_MEASURES dimension
UNIT_PRICE
NUMBER(15,5)
Unit price
FRGHT_PRORATE_FLG
VARCHAR2(3)
Prorate freight charges
DSCNT_APPL_FLG
VARCHAR2(3)
Apply discount
BUSINESS_UNIT_RECV_K EY
NUMBER
Receiving business unit surrogate key to the BUSINESS_UNIT dimension
RECEIVER_ID
VARCHAR2(30)
Receipt number
RECV_LN_NBR
INTEGER
Receipt line
AMT_ONLY_FLG
VARCHAR2(3)
Amount only
TXN_CURRENCY_KEY
NUMBER
Transaction currency surrogate key to the CURRENCY dimension
WITHHOLDING_KEY
NUMBER
Surrogate key to the WITHHOLDING dimension
BASE_CURRENCY_KEY
NUMBER
Base currency surrogate key to the CURRENCY dimension
CURRENCY_KEY
NUMBER
Surrogate key to the CURRENCY dimension
TIME_KEY
NUMBER
Surrogate key to TIME_DIMENSION
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
165
A
A
Rapid Mart Data Schema WITHHOLDING
WITHHOLDING Dimension with Payemnt Withholding attributes Column name
Key
Data type
Column description
WITHHOLDING_KEY
PK
NUMBER
Surrogate key toWITHHOLDING
SETID
VARCHAR2(15)
SetID
WTHD_CD
VARCHAR2(15)
Withholding code
DESCR
VARCHAR2(90)
Description
LOAD_DATE
DATETIME
Date and Time when the record was loaded
LOAD_TIME
VARCHAR2(8)
Time in a string format when the record was loaded
166
BusinessObjects PeopleSoft Accounts Payable Rapid Mart User’s Guide
