User manual BUSINESS OBJECTS ACCOUNTS PAYABLE RAPID MART 11.5.4.0

[. . . ] BusinessObjects Accounts Payable Rapid Mart User's Guide Version 11. 5. 4. 0 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. 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. [. . . ] The hierarchies appear both as sub-classes in the common dimensions of the universe and in their own display screen in Web Intelligence (as shown below). The universe contains 4 hierarchies. BusinessObjects Accounts Payable Rapid Mart User Guide 135 5 Universe Hierarchies Accounts Payable Rapid Mart Hierarchies Fiscal Periods: A hierarchy on the Fiscal Periods Dimenison. Note: In Vendor Totals this can only be used for reporting vertical totals as the horizontal values do not have a fiscal period. Document Date Time Dim: A hierarchy on the Time Dimension associated with the document date on Vendor Items, Vendor Offset, Checks, or Purchase Orders. Posting Date Time Dim: A hierarchy on the Time Dimension associated with the posting date on Vendor Items, Vendor Offset, or Purchase Orders. Check Cashed on Date Time Dim: A hierarchy on the Time Dimension associated with the datea a Check was cashed. Vendor Geography: A hierarchy on the geography associated with the vendor's address. 136 BusinessObjects Accounts Payable Rapid Mart User Guide Universe Filters 5 Filters The universe contains filters that allow you to restrict the results of your reports. The filters appear after the Fact Table Classes in the universe: Vendor Item Filters: These allow you to filter by Open or Closed items. You can also filter for payment documents only. Finally, you can also filter for unapplied payments or credit memos. Financial Document Type Filters: These filters restrict the Financial Document Type which is a dimension of Vendor Items. You can restrict to either Revenue or Payment document types. BusinessObjects Accounts Payable Rapid Mart User Guide 137 5 Universe Customizing the Universe Customizing the Universe The universe can be modified easily to suit your specific needs. De-Linking Vendor Totals Tables: The VEND_PER_TTL_FACT and VEND_PER_TTL_FACT_VR tables are linked, with most dimensions joined to the VEND_PER_TTL_FACT table. We did this to simplify the universe design by creating only one context for Vendor Totals. In some cases, it may be advantageous for system performance to delink these tables, and create separate contexts for Vendor Totals (Vertical)and Vendor Totals (Horizontal). Remove the join between VEND_PER_TTL_FACT and VEND_PER_TTL_FACT_VR. Duplicate all joins between VEND_PER_TTL_FACT and the dimension tables, but change the fact table in these joins to VEND_PER_TTL_FACT_VR Duplicate all joins between VEND_PER_TTL_FACT_VR and the dimension tables, but change the fact table in these joins to VEND_PER_TTL_FACT. Create a new context for VEND_PER_TTL_FACT_VR. Make sure all joins for VEND_PER_TTL_FACT are in the VEND_PER_TTL_FACT context. Make sure all joins for VEND_PER_TTL_FACT_VR are in the VEND_PER_TTL_FACT_VR context. 3. 4. 6. View for DPO (Days Payables Outstanding) Calculation The universe contains a database view VENDOR_ITEMS_AP_BALANCE_VIEW (on VENDOR_ITEMS_FACT). It is joined to the VEND_PER_TTL_FACT_VR table and used in the calculation of DPO (Days Payables Outstanding). The view was created so that we can obtain the open Accounts Payable balance for a vendor. [. . . ] State) NVARCSTR 20 0 0 yes 24 STREET Street address NVARCSTR 35 0 0 yes 25 TELEPHONE_1 First telephone number NVARCSTR 16 0 0 yes 26 TELEPHONE_2 Second telephone number NVARCSTR 16 0 0 yes 27 TRADING_PARTNER_I D Trading partner ID NVARCSTR 6 0 0 yes 28 VENDOR_ID PK Vendor number NVARCSTR 10 0 0 no 29 VENDOR_NAME_1 Vendor name line 1 NVARCSTR 35 0 0 yes 30 VENDOR_NAME_2 Vendor name line 2 NVARCSTR 35 0 0 yes 31 VENDOR_TITLE Vendor title NVARCSTR 15 0 0 yes 32 ZIP Zip Code (Postal Code) NVARCSTR 20 0 0 yes Rapid Mart Data Schema 34 Appendix Table Column Details A VENDOR_BY_COMPANY General vendor master data at the vendor at company code level # Column Name Key Column Desc Column Datatype Dtype Length Precision Scale Is Nullable 1 ACCOUNTING_CLERK PK T001S. BUSAB Accounting clerk NVARCSTR 2 0 0 yes 2 ACCT_GRP_ONE_TIM E LFA1. KTOCK Reference account group for onetime account (vendor) LFA1. LNRZA Account number of the alternative payee NVARCSTR 4 0 0 yes 3 ALT_PAYEE_ID NVARCSTR 10 0 0 yes 4 CENTRAL_DELETE_B LOCK CENTRAL_PAYMENT_ BLOCK CENTRAL_POSTING_ BLOCK_ID CENTRAL_PURCHASI NG_BLOCK CHART_OF_ACCTS_I PK D LFA1. NODEL Central Deletion Block NVARCSTR 1 0 0 yes 5 LFA1. SPERZ Central Payment Block NVARCSTR 1 0 0 yes 6 LFA1. SPERR Central Posting Block NVARCSTR 1 0 0 yes 7 LFA1. SPERM Central Purchasing Block NVARCSTR 1 0 0 yes 8 T001. KTOPL Chart of Accounts (Company Code) NVARCSTR 4 0 0 yes 9 COMPANY_CODE_ID PK LFB1BUKRS Company code NVARCSTR 4 0 0 no 10 COMP_CODE_DELETI ON_BLOCK COMP_CODE_POST ING_BLOCK LFB1. NODEL Company Code Level Deletion NVARCSTR Blcok LFB1. SPERR Company Code Level Posting Block 1 0 0 yes 11 NVARCSTR 1 0 0 yes 12 CREDIT_INFO_ID LFA1. KRAUS Credit information number NVARCSTR 11 0 0 yes 13 CUSTOMER_ID LFA1. KUNNR Customer number NVARCSTR 10 0 0 yes 14 DELETED_IND LFB1. LOEVM Central deletion flag for master record NVARCSTR 1 0 0 yes 15 FACTORY_CAL_ID LFA1. PLKAL Factory calendar key NVARCSTR 2 0 0 yes 16 GL_RECONCILE_ACC PK T SKB1. SAKNR Account Code NVARCSTR 10 0 0 yes Rapid Mart Data Schema 35 Appendix Table Column Details Column Datatype Dtype Length Precision Scale A # Column Name Key Column Desc Is Nullable 17 HOUSE_BANK_ID LFB1. HBKID ID for one house bank NVARCSTR 5 0 0 yes 18 INTL_LOC_NUM_1 LFA1BBBNR International location number (part 1) LFA1BBBSR International location number (part 2) LFB1. EIKTO Our account number at the vendor NVARCSTR 7 0 0 yes 19 INTL_LOC_NUM_2 NVARCSTR 5 0 0 yes 20 OUR_ACCOUNT_NUM NVARCSTR 12 0 0 yes 21 PAYMENT_BLOCK_IN D LFB1. ZAHLS Block key for payment NVARCSTR 1 0 0 yes 22 PAYMENT_TERMS_ID PK Payment terms key NVARCSTR 4 0 0 yes 23 PLANT_ID PK T001W. WERKS Plant NVARCSTR 4 0 0 yes 24 POR_ID LFA1. ESRNR POR subscriber number NVARCSTR 11 0 0 yes 25 SOLE_PROPR_IND LFA1. STKZN Indicator if partner is a sole proprietor NVARCSTR 1 0 0 yes 26 TAX_JURIS_ID LFA1. TXJCD Jurisdiction for tax calculation NVARCSTR tax jurisdiction code 15 0 0 yes 27 TAX_NUM_1 LFA1. STCD1 Tax number 1 NVARCSTR 16 0 0 yes 28 TAX_NUM_2 LFA1. STCD2 Tax number 2 NVARCSTR 11 0 0 yes 29 VAT_REG_NUM LFA1. STCEG VAT registration number NVARCSTR 20 0 0 yes 30 VENDOR_ID PK LFA1LIFNR Vendor (creditor) account number NVARCSTR 10 0 0 no Rapid Mart Data Schema 36 Appendix Table Column Details A VENDOR_ITEMS_AP_BALANCE_VIEW Vendor Items AP Balance View # Column Name Key Column Desc Column Datatype Dtype Length Precision Scale Is Nullable 1 COMPANY_CODE_ID Company code NVARCSTR 4 0 0 no 2 FISCAL_PERIOD Fiscal Period in SAP DECIMAL 48 28 0 yes 3 FISCAL_YEAR Fiscal Year in SAP NVARCSTR 4 0 0 no 4 ROLLING_12_TTL Rolling 12 Period Total Local Currency DECIMAL 48 28 7 yes 5 SALES_TTL Total Sales per Period (Local Currency) DECIMAL 48 28 7 yes 6 SUM_LOCAL_CURR ENCY_AMT SUM_LOCAL_CURR ENCY_TAX SUM_TOTAL_AMT_DU E Sum of Local Currency Amount DECIMAL 48 28 7 yes 7 Sum of Local Currency tax amount DECIMAL 48 28 7 yes 8 Sum of (Local Currency Amt + Local Currency Tax Amt) DECIMAL 48 28 7 yes 9 VENDOR_ID Vendor NVARCSTR 10 0 0 no Rapid Mart Data Schema 37 Appendix Table Column Details A VENDOR_ITEMS_FACT Financial documents (such as invoices and payments) regarding vendors # Column Name Key Column Desc Column Datatype Dtype Length Precision Scale Is Nullable 1 AGING_INTERVAL_ID PK Aging Interval ID DECIMAL 48 28 0 yes 2 ALLOC_ID PK Allocation ID NVARCSTR 18 0 0 no 3 AMT_DUE_IN_E_DAY S AMT_DUE_IN_F_DAY S AMT_DUE_IN_G_DAY S AMT_DUE_OVER_G _DAYS Amount Due in upto "E" Days DECIMAL 48 13 2 yes 4 Amt Due between "E" and "F" days DECIMAL 48 13 2 yes 5 Amt Due between "F" and "G" days DECIMAL 48 13 2 yes 6 Amount due in more than interval "G" days DECIMAL 48 13 2 yes 7 AMT_ELIG_FOR_DISC Amount of invoice that can be discounted as appropriate Local currency amount paid during the first discount period (includes any discounts applicable) Local currency amount paid after the first and during the second discount period (includes any discounts applicable) Paid after due date Local currency amount paid before due date and after any discount periods (includes only nontimerelated discounts Local currency amount paid by due date (includes any discounts applicable) Local currency amount paid after the first measuring period after due date Local currency amount paid after the second measuring period after due date Local currency amount paid after the third measuring period after due date Local currency amount paid after the fourth measuring period after due date DECIMAL 48 13 2 yes 8 AMT_PAID_DISCOUN T1 AMT_PAID_DISCOUN T2 DECIMAL 48 13 2 yes 9 DECIMAL 48 13 2 yes 10 AMT_PAID_LATE DECIMAL 48 13 2 yes 11 AMT_PAID_NET DECIMAL 48 13 2 yes 12 AMT_PAID_ON_TIME DECIMAL 48 13 2 yes 13 AMT_PD_GT_A_LATE DECIMAL 48 13 2 yes 14 AMT_PD_GT_B_LATE DECIMAL 48 13 2 yes 15 AMT_PD_GT_C_LATE DECIMAL 48 13 2 yes 16 AMT_PD_GT_D_LATE DECIMAL 48 13 2 yes Rapid Mart Data Schema 38 Appendix Table Column Details Column Datatype Dtype Length Precision Scale A # Column Name Key Column Desc Is Nullable 17 BASELINE_DATE Date from which all due and late dates are calculated Document number of the associated billing document DATETIME 24 0 9 yes 18 BILLING_DOC_ID NVARCSTR 10 0 0 yes 19 BUSINESS_AREA_ID PK TGSBGSBERBusiness area NVARCSTR 4 0 0 yes 20 CASH_DISCOUNT_1 Amount of discount eligible if paid by first discount period Amount of discount eligible if paid by second discount period T001. KTOPL Chart of Accounts (Company Code) DECIMAL 48 5 3 yes 21 CASH_DISCOUNT_2 DECIMAL 48 5 3 yes 22 CHART_OF_ACCTS_I PK D NVARCSTR 4 0 0 yes 23 CLEARED_FLAG PK "O" if item is open "C" if item is cleared NVARCSTR 1 0 0 yes 24 CLEARED_ON PK Date document cleared DATETIME 24 0 9 no 25 CLEARING_DOC_ID PK Document number of the clearing document NVARCSTR 10 0 0 no 26 COMPANY_CODE_ID PK Company code NVARCSTR 4 0 0 no 27 CURRENCY_ID PK TCURCWAERS Currency Key NVARCSTR 5 0 0 yes 28 DEB_CRED_IND Debit or credit indicator NVARCSTR 1 0 0 yes 29 DISCOUNT_PERIOD_ 1 DISCOUNT_PERIOD_ 2 Number of days in the first discount period DECIMAL 48 28 0 yes 30 Number of days in the second discount period DECIMAL 48 28 0 yes 31 DISC_AMT_TAKEN Discount amount taken DECIMAL 48 13 2 yes 32 DOCUMENT_DATE Date of the actual document DATETIME 24 0 9 yes 33 DOC_CURRENCY_A MT Amount in document currency DECIMAL 48 13 2 yes Rapid Mart Data Schema 39 Appendix Table Column Details Column Datatype Dtype Length Precision Scale A # Column Name Key Column Desc Is Nullable 34 DOC_CURRENCY_T AX Tax amount in document currency DECIMAL 48 13 2 yes 35 ENTRY_DATE Date of entry by accounting DATETIME 24 0 9 yes 36 FINANCIAL_DOC_ID PK Financial document number NVARCSTR 10 0 0 no 37 FIN_DOC_TYPE_ID Financial document type NVARCSTR 2 0 0 yes 38 FISCAL_PERIOD PK Fiscal Period in SAP DECIMAL 48 28 0 yes 39 FISCAL_YEAR PK Fiscal Year in SAP NVARCSTR 4 0 0 no 40 FISC_YR_VARIANT_ID PK Fiscal Year Variant in SAP NVARCSTR 2 0 0 yes 41 GLOBAL_CURRENC Y_ID GLOBAL_EXCHANG E_TYPE_ID Global Currency ID from $G_GLOBAL_CURRENCY variable Global Exchg. Rate Type from $G_EXCHANGE_TYPE_ID variable NVARCSTR 5 0 0 yes 42 NVARCSTR 4 0 0 yes 43 GL_ACCOUNT_ID PK SKB1. SAKNR Account Code NVARCSTR 10 0 0 yes 44 GROUP_CURRENCY _ID BKPF. KZWRS Group Currency ID NVARCSTR 5 0 0 yes 45 HOUSE_BANK_ID ID for one house bank NVARCSTR 5 0 0 yes 46 INVOICE_ID Document number of associated invoice NVARCSTR 10 0 0 yes 47 LINE_ITEM_ID PK Financial document line item number NVARCSTR 5 0 0 no 48 LINE_ITEM_TEXT Descriptive text of the line item NVARCSTR 50 0 0 yes 49 LOAD_DATE Date that this row was loaded into the data warehouse Time that this row was loaded into the data warehouse DATETIME 24 0 9 yes 50 LOAD_TIME NVARCSTR 10 0 0 yes Rapid Mart Data Schema 40 Appendix Table Column Details Column Datatype Dtype Length Precision Scale A # Column Name Key Column Desc Is Nullable 51 LOCAL_CURRENCY _AMT LOCAL_CURRENCY_I D LOCAL_CURRENCY_T AX Amount in local (company) currency DECIMAL 48 13 2 yes 52 BKPF. HWAER Local Currency ID NVARCSTR 5 0 0 yes 53 Tax amount in local (company) currency DECIMAL 48 13 2 yes 54 NET_DUE_DATE Baseline date + Net due period in days DATETIME 24 0 9 yes 55 NET_DUE_PERIOD Number of days between second discount period and net due date (SAP value) DECIMAL 48 28 0 yes 56 NEW_NET_DUE_PERI OD Creates new net due period for those payment DECIMAL terms that leave the net due period blank. Number of days after NEW NET DUE DATE amount was paid Number of days the item was/is open: LOADDATE POSTING_DATE if still open CLEARING_DATE POSTING_DATE if cleared. 48 28 0 yes 57 NUMBER_DAYS_LATE DECIMAL 48 28 0 yes 58 NUMBER_DAYS_OP EN NUM_DAYS_PAST_ DUE PAST_DUE_AMT_GT_ A PAST_DUE_AMT_GT_ B PAST_DUE_AMT_GT_ C PAST_DUE_AMT_GT_ D PAYMENT_BLOCK_IN PK D DECIMAL 48 28 0 yes 59 Number of days since due date if item still open DECIMAL and past due Local currency amount if the first measuring period after net due date has past 48 28 0 yes 60 DECIMAL 48 13 2 no 61 Local currency amount if the second measuring DECIMAL period after net due date has past Local currency amount if the third measuring period after net due date has past Local currency amount if the fourth measuring period after net due date has past 48 13 2 yes 62 DECIMAL 48 13 2 yes 63 DECIMAL 48 13 2 yes 64 T008T. ZAHLS Block key for payment NVARCSTR 1 0 0 yes 65 PAYMENT_IND Indicator if this is a payment NVARCSTR 1 0 0 yes 66 PAYMENT_METHOD Payment method NVARCSTR 1 0 0 yes 67 PAYMENT_REASON _CODE_ID PK T053S. RSTGR Reason Code for Payments NVARCSTR 3 0 0 yes Rapid Mart Data Schema 41 Appendix Table Column Details Column Datatype Dtype Length Precision Scale A # Column Name Key Column Desc Is Nullable 68 PAYMENT_REFERE NCE_ID BSIK. KIDNO or BSAK. KIDNO Payment Ref. ID. NVARCSTR 30 0 0 yes 69 PAYMENT_TERMS_ID PK Payment terms key NVARCSTR 4 0 0 yes 70 POSTING_DATE PK Date the entry hits the accounting books DATETIME 24 0 9 no 71 POSTING_KEY_ID PK Posting key NVARCSTR 2 0 0 yes 72 REF_DOC_ID Reference document number NVARCSTR 16 0 0 yes 73 REVERSAL_IND Indicator: Document is flagged for reversal NVARCSTR 1 0 0 yes 74 SPECIAL_GL_ACCOU NT_ID BSIK. SAKNR or BSAK. SAKNR Special GL Acct. [. . . ]