In order to bring you the best possible user experience, this site uses Javascript. If you are seeing this message, it is likely that the Javascript option in your browser is disabled. For optimal viewing of this site, please ensure that Javascript is enabled for your browser.
Login  |   Cloud ERP  |   Home  |   qad.com



  •     QAD Glossary

  • Release Notes for Current Release
    QAD Business Intelligence Version: 3.8.1
    Date: March 2013
    New Feature Summary
    For the QAD BI 3.8.1 release, the EE financial module has been redesigned and redeveloped. For more information regarding this module, see the QAD BI Modules and Key Performance Indicators User Guide.
    Upgrade Instructions
    See the QAD BI 3.8.1 Installation Guide for the instructions to upgrade to QAD BI 3.8.1.
    Data Warehouse Tables Changed
    The following section lists the Data Warehouse tables that have been changed, added, or deleted.
    Note: The tables on the Modified list may have been changed structurally. For example:
    Columns added or deleted
    Indexes added or deleted
    Changes made to business display names
    Changes to visibility of particular columns in the BI Portal
    Any combination of these modifications
    The procedure that populates the table has changed
    List of Tables Modified
    Common Module Modified Tables
     
    dim_business_relation
    load_tr_hist_history
    stage_daybook
    dim_credit_term
    load_tr_hist_list
    stage_eam_date_qad3
    dim_customer
    load_transaction_type
    stage_item
    dim_date_qad
    perm_code_master
    stage_item_costs
    dim_daybook
    perm_item_master
    stage_item_list
    dim_ee_invoice_status
    perm_shop_cal
    stage_item_master
    dim_ee_saf
    perm_transaction_hist
    stage_item_site
    dim_item
    stage_ar_master
    stage_layer
    dim_layer
    stage_business_relation
    stage_project
    load_ar_mstr
    stage_calendar_interval_week
    stage_se_ar_master
    load_in_mstr
    stage_code_master
    stage_se_cost_center
    load_ld_det
    stage_credit_term
    stage_se_customer
    load_pt_mstr
    stage_customer
    stage_supplier
    load_tr_hist
    stage_date_qad
    stage_transaction_hist
     
    Financials Module Modified Tables
     
    dim_ap_transaction_type
    ods_gen_led_rep_line_calcs
    stage_gen_led_report_line5
    dim_ar_transaction_type
    ods_gen_led_rep_line_range
    stage_gen_led_report_line6
    extract_gl_balance
    stage_gen_led_rep_code_comb
    stage_se_ap_snap1
    load_acd_det
    stage_gen_led_rep_line_ran
    stage_se_ar_snap1
    load_bgd_det
    stage_gen_led_rep_line_ran1
    work_ap_change_list
    load_gen_led_report
    stage_gen_led_rep_line_ran2
    work_ap_change_list1
    load_gen_led_report_line
    stage_gen_led_rep_line_range
    work_ap_change_list2
    load_gen_led_report_line_calcs
    stage_gen_led_report_line
    work_ap_change_list3
    load_gen_led_report_line_range
    stage_gen_led_report_line1
    work_ap_change_list4
    load_gltr_hist_history
    stage_gen_led_report_line2
    work_ap_change_list5
    ods_gen_led_rep
    stage_gen_led_report_line3
    work_gl_trans_change_list
    ods_gen_led_rep_line
    stage_gen_led_report_line4
     
    Operations Module Modified Tables
     
    dim_buyer
    perm_mrp_detail
    stage_mrp_calculations1
    fact_inv_mth_balance
    permsup_mrp_detail
    stage_mrp_calculations2
    fact_inv_transaction
    stage_inv_bal_mth_date
    stage_po_order_hist
    fact_op_item_efficiency
    stage_inv_mth_balance_hist_calc
    stage_po_order_hist1
    fact_po_order
    stage_inv_trx_chg_mths
    stage_po_order_hist5
    fact_po_order_history
    stage_mrp_calculations
    stage_po_receipt3
    Order Management Module Modified Tables
     
    extract_om_transaction_hist
    stage_om_invoice3
    stage_om_order_inv_line2
    fact_om_booking
    stage_om_ord_perf
    stage_om_order_line_all
    fact_om_invoice
    stage_om_ord_perf_curr
    stage_om_order_snap
    fact_om_order
    stage_om_ord_perf_curr1
    stage_om_order_snap1
    fact_om_order_history
    stage_om_ord_perf_date
    stage_om_order_snap2
    fact_om_order_performance
    stage_om_ord_perf_date1
    stage_om_order_snap3
    fact_om_shipment
    stage_om_ord_perf_full
    stage_om_order_transaction
    perm_om_invoice_line
    stage_om_ord_perf_ship
    stage_om_order_transaction1
    stage_om_booking_orig_order
    stage_om_ord_perf1
    stage_om_order_transaction2
    stage_om_booking_transaction
    stage_om_order
    stage_om_order_transaction3
    stage_om_booking_transaction1
    stage_om_order_header
    stage_om_order_transaction4
    stage_om_booking_transaction2
    stage_om_order_header_all
    stage_om_order_transaction5
    stage_om_booking1
    stage_om_order_hist_end_date
    stage_om_order1
    stage_om_invoice
    stage_om_order_hist_revalue
    stage_om_order3
    stage_om_invoice_line_list
    stage_om_order_hist_revalue2
    stage_om_shipment
    stage_om_invoice_line_ranged
    stage_om_order_hist_revalue3
    stage_om_shipment_transaction
    stage_om_invoice_order_line
    stage_om_order_hist_revalue4
    stage_om_shipment1
    stage_om_invoice_order_list
    stage_om_order_inv_header
    stage_om_shipment2
    stage_om_invoice_transaction
    stage_om_order_inv_line
    stage_om_shipment3
    stage_om_invoice_transaction1
    stage_om_order_inv_line1
    work_om_change_list1
    stage_om_invoice2
     
     
    Tables Added to Existing Modules
    Common Module Added Tables
     
    load_com_mstr
    load_ee_pti_det
    stage_pti_det
    load_comd_det
     
     
    For the 3.8.1 release, all EE financial-related tables and jobs have been deleted and replaced.
    Order Management Module Added Tables
     
    load_tr_hist_remarks
    stage_om_order_transaction6
    Financials Module Added Tables
    For the 3.8.1 release, all EE financial-related tables and jobs have been deleted and replaced.
    Operations Module Added Tables
     
    dim_po_buyer
    stage_po_buyer_code_master
    stage_po_buyer_po_header
    stage_po_buyer
     
     
    List of Tables Deleted
    For the 3.8.1 release, all EE financial-related tables and jobs have been deleted and replaced.
    Enhancements
     
    Table 1 BI 3.8.1 Enhancements (1 of 2)
    Component
    QAD Issue
    Description
    Metadata
    (Multimodule)
    QBI-744
    Added logic to display commodity code and description by tying the part number back to the comd_det and com_mstr tables.
     
    QBI-1233
    Adds five missing columns that previously were missing from dim_item.
    promotion_group_description
    supplier_description
    buyer_description
    abc_class
    safety_time
    Also changes the logic when determining whether to display data. Prior logic was if there was a link to ptp_det and a particular column was null, then the pt_mstr equivalent column would be displayed. This has been corrected to display null if the ptp_det record had a null in a given column because the intent of ptp_det is to have its data override what is in pt_mstr.
    If the in_mstr column is null, a recent change to include data from in_mstr, howeve,r is typically overridden by the pt_mstr data.
    For ABC code, which comes from pti_det, it displays the pt_mstr.pt_abc value if the item_site level join of pti_det to ptp_det or in_mstr produces a null for abc_code.
     
    QBI-1555
    Added new column to dim_item for Pricing Break Category, from pt_mstr.pt_break_cat.
     
    QBI-2447
    Business Display Name for the dim_item and the dim_location tables have been changed to reflect that those tables are site-item and site-location. The label of the table was misleading because people were not realizing that the grain of the table included site.
     
    QBI-2818
    In BI, Order Management Invoices use financials records to get the effective date of the invoice. Before BI 3.8.1, there was not a complete solution for Enterprise Financials. Now that EE Financials is implemented, records from DInvoice go into our common BI table perm_ar_master in addition to records from ar_mstr. The effective date can come from either DInvoice.DInvoicePaymentDate or ar_mstr.ar_effdate depending on the source type. If no Financials records match, the effective date of the invoice is the invoice entered date.
    Metadata
    (Order Management)
    QBI-2305
    This change historically populates any missing remarks columns in tr_hist for ISS-SO records as well as populating any missing remarks going forward on the daily runs. The need for this logic arises as shipped orders are not always invoiced on the day they are shipped and when the invoice finally is created the tr_hist record is updated with the invoice number in the remarks table. We use sales process days to check how far back we look for new invoices. In order to catch those invoices in load_ih_hist, load_idh_hist, and load_tr_hist_remarks, the SALES_PROCESS_DAYS parameter should be set to a number that would be greater than the standard number of days an invoice may be back dated.
    Metadata
    (EE Financials)
    QBI-2858
    Upgrade - BI3.7 to BI3.8.1
    The upgrade process deletes all “EE” objects in the current Data Warehouse. Then, new EE Objects are added, as needed.
    Any customers currently running the Financials module against any EE source system cannot upgrade using the standard upgrade files. Contact your QAD Services group.
    Specifically, the following fact and perm tables are deleted:
    fact_ee_ap_invoice
    fact_ee_ap_invoice_history
    fact_ee_ap_invoice_snapshot
    fact_ee_ap_payment
    fact_ee_ar_invoice
    fact_ee_ar_invoice_history
    fact_ee_ar_invoice_snapshot
    fact_ee_ar_payment
    fact_ee_gl_balance
    fact_ee_gl_transaction
    ods_ee_ap_balance
    ods_ee_ar_balance
    perm_ee_credit_invoice
    perm_ee_credit_invoice_move
    perm_ee_debit_invoice
    perm_ee_debit_invoice_move
    perm_ee_posting
    perm_ee_posting_history
    perm_ee_posting_line
    permsup_ee_credit_invoice
    permsup_ee_credit_invoice_move
    permsup_ee_debit_invoice
    permsup_ee_debit_invoice_move
    permsup_ee_posting
    permsup_ee_posting_line
    Fixes
     
    Table 2 BI 3.8.1 Fixes (1 of 4)
    Component
    QAD Issue
    Description
    Metadata
    (Financials EE)
    QBI-2814
    Table load_ee_unit modified. Custom fields removed from table.
    Metadata
    (Financials SE)
    QBI-792
    The GL_LOAD_LOOKBACK_DAYS and GL_LOAD_DAYS parameters, which are set very high at startup and then shrunk to some much smaller value (like 50 & 52) before running the first DAILY run, are prone to misconfiguration when changing the values. It is critical that the GL_LOAD_DAYS parameter is greater than the GL_LOOKBACK_LOAD_DAYS parameter. If it is not, it is possible that the code may start a second load loop that it should not and create all sorts of weird load headaches. To ensure end users do not inadvertently set these parameters incorrectly, an extra check was put in place that checks the parameters at the beginning of each GL run. If the GL_LOAD_DAYS parameter is NOT greater than the GL_LOAD_LOOKBACK_DAYS parameter, the first task of the load will fail with an error message indicating such and the end user will have to correct the problem before they can successfully get past that first task in the job.
     
    QBI-2190
    Parameters AP_SE_SNAPSHOT_HIST_START_DATE and AR_SE_SNAPSHOT_HIST_START_DATE are now used during the History load processing. These parameters should be set prior to running HIST_START for any source.
     
    QBI-2226
    The logic to perform the daily update the fact_ap_voucher_history file has been modified to use the effective date on the voucher or check, instead of the current date.
    A new parameter has been added to replace the use of the current date function (GETDATE). This parameter is named AP_SE_HISTORY_PROCESS_DATE and by default is set to the current date at the beginning of the DAILY run. To use a different date for testing, the parameter DAILY_HISTORY_DATE_SET_CURRENT should be set to 'N' and the desired test date should be entered in the new parameter, AP_SE_HISTORY_PROCESS_DATE. The effective date on the voucher or check must be within the processing window, as defined as the AP_SE_HISTORY_PROCESS_DATE minus the AP_PROCESS_DAYS. Otherwise, the value in AP_SE_HISTORY_PROCESS_DATE is used.
    An upgrade script that truncates and rebuilds the fact_ap_voucher_history and fact_ap_invoice_snapshot tables has been provided. It is a series of four jobs. UPGRADE_BI37_TO_BI381_FIN_SE. This is an OPTIONAL step in an upgrade. If the customer has not experienced any issues related to AP Aging reporting, they do not need to run this job.
    Metadata
    (Inventory)
    QBI-642
    Golden Boy says that our standard code for handling fact_inv_mth_balance.days_on_hand_projected does not work for their use of MRP. MRP schedules future orders for items consumed regularly. The BI report fact_inv_mth_balance takes an average of these proposed future orders and compares that with current inventory of an item to see how many "days left" there are in inventory. Golden Boy may have orders some two years or more in the future, but they taper off when projecting that far out. They believe that it is more realistic to average the next 180 days or so to get a more accurate picture.
    With that in mind, we are making an enhancement to narrow the future order search to 180 days. This requires a code change several staging tables back in the meta data track-back.
     
    QBI-2170
    Before this release, if inventory transactions were entered for dates in the future, the DAILY_OP_INV_PROCESS_CHAINED job would fail. This has been resolved in BI 3.8.1. Future inventory transactions are included in the Inventory Transaction file (fact_inv_transaction file), but are not included in the Inventory Balances Fact Monthly Snapshot (fact_inv_mth_balance) table.
    Metadata
    (Multimodule)
    QBI-2521
    Many measures in various fact tables in BI 3.7 do not appear in the BI Portal. This is a problem with the Additive Flag in the column metadata. In addition, attributes of both fact and dimension tables no longer appear. This is a problem with the Attribute Flag in the column metadata.
    When a user attempts to run a query that includes one of the affected columns, they receive an error message.
    These flags have been fixed for BI 3.8.1. A script is available from QAD Support that fixes this problem in a BI 3.7 database.
     
    QBI-2578
    DWD 6.6.2—Problem resolved with end-of-year processing. Arithmetic overflow error during DAILY_COMMON_PROCESS_CHAINED jobs, task = daily_date_roll - Process. New release of DWD modified row in dim_date with dim_date_key = 0. Calendar_date value for this row is now set to the oldest possible date value, 1/1/1753. This caused the daily_date_roll procedure to fail during year end processing. The procedure was modified to handle this date value.
     
    QBI-2708
    This resolves the issue where a customer does not populate all their site/item combinations in the ptp_det table. ptp_det is used for planning and many customers instead only populate those combinations in in_mstr. So in_mstr has been added as a source for the dim_item table by feeding into stage_item_site. This ensures that we do not have missing item/site combinations when populating various fact tables, in particular for customers that have only the OP (operations) modules, but not OM (order management) as the OM module had an extra feature ensuring that dim_item was populated from transactional_data which likely ensures that the in_mstr records were getting picked up but with less detail. This also resolves the issue where transactional only site/item combinations were not displaying site description while the same site code for a different item would display site_description. Now if a site_code exists for transactional data, the site_description should exist as well (if there is one).
    Metadata
    (Order Management)
    QBI-1799
    Parameter SALES_PROCESS_DAYS now defaults to 2. This allows the DAILY to avoid restaging orders over and over for more than two days, which is especially important during the days immediately after installation where all the history records are stamped with the same day. If the daily fails to run for one or more days, this parameter needs to be increased to cover all the missed days. DAILY_OM_PERM_CHAINED has a procedure setp_order_history_date() that verifies the current date against the parameter SALES_ORDER_HISTORY_DATE and fails/prompts the user to increase the number of days staged in SALES_PROCESS_DAYS. Once the history load is more than 10 or 20 days in the past, it is safe to leave this parameter at 10 or so and allow it to reprocess a few days worth of sales transactions every day.
    The SALES_ORDER_HISTORY_DATE is based on the system date of the server where BI scheduled jobs are located. The date is adjusted by -0.35 days to compensate for runs done between midnight and 8:20AM server time. Any time DAILY_OM_PERM_CHAINED is run before 8:20AM server time, the data is considered to be current as of yesterday's date. If ran after 8:20AM, possibly due to a connection/network error and retried, the data is considered valid as of the current date. Customers have expressed concern in the past regarding whether the BI report data is current as of yesterday or today, but as a rule BI does not contain the current day's transactions.
     
    QBI-2162
    Multiple changes were made to address issues around the following:
    Existing logic was filtering out Invoices based on the Invoice rank in relation to a given source/domain/order/line combination on a given day. So if an order had multiple invoices for an order on a given day, such as the order got shipped and invoiced against twice, some of the order lines from earlier invoices in the day may not make it to the fact because the last invoice for the order of that day did not include those lines. The filter on rank was removed so that all invoices for a given day could feed to the next two subsequent staging tables at which point we rank the invoice by order AND line. If there are multiple invoices against that order in a given day, we are able to pick up every line associated with an order .
    We also move the quantity_shipped value from the stage2 table where it was rolling up multiple shipped values from multiple invoices into one value (shipped is already cumulative), which was providing wrong shipped amounts to the stage1 table where we would only get the most recent shipped value for a given line instead.
    We also added a conversion to data type of FLOAT for any division or multiplication that was necessary for UM conversion, prices, or costs so that we did not suffer the pervasive rounding errors that we have had in the past. See also QBI-2448.
    Final result—There should not exist in the perm_om_invoice_line or stage_om_order_line_tables, any instances of source/domain/order/line/item that do not also exist in fact_om_order_history. One other scenario (QBI-2830) involving the stage_om_order_transaction3-6 tables, in which a sales order or invoice exists but the tr_hist records related to it is archived, can cause the above-mentioned query to produce results not tied to this fix. See affiliated UnitTestDoc for this master Jira for script to test this result.
     
    QBI-2193
    Due to the reuse of order number in OM, dim_item_key was added to the fact_om_order_history table as part of the business key in 3.7. Unfortunately, dim_item_key is made up of both item_number and site_code and site_codes change frequently. As a result, item_number was added as an additional column and the business key was changed to include item_number instead of dim_item_key. This fix ensures that if order numbers are being reused for different item_numbers, then the orders are recognized as two different orders. However, if an item_number is changed for an existing order, the fact_om_order_history table closes the original order number and open this order with a different item_number as a different order altogether. This should still be fairly trackable in the order history table, and ensures that an order number used two years ago is not being treated as though it was reopened if a new one with the same number (but different item) is created.
     
    QBI-2414
    Parameter SALES_SNAPSHOT_HIST_START_DATE is now used during the History load processing. This parameter should be set before running HIST_START for any source.
     
    QBI-2448
    This fix corrects a problem with the code in the instances where the total quantity invoiced for an source/domain/order/line combination is 0 and the system displays a 0 for price, cost, and unit of measure conversion fields. Those fields are now populated with the average of their source column so that those values are reflected properly at the fact_om_invoice table.
     
    QBI-2830
    1. Prior releases had been setting the sales_order_history_date based on the SALES_ORDER_HISTORY_DATE parameter for any records that were created when tr_hist records did not exist for either sales orders or invoices. This was incorrect as the sales_order_history_date would change every day. Changes were made to pick up the invoice date or order date for those faked tr_hist records in the stage_om_order_transaction4-6 tables.
    2. There was no easy way to link which order number/line combination, which was picked from the various transaction, directly to a given invoice. To handle this, stage_om_order_transaction tables had the invoice_date and invoice_number columns added so that they could be linked to any invoices that have existed. Previously it would guess based on source/domain/order/line/item. The invoice number comes from tr_hist.tr_remarks.
    3. Stage_om_order1 custom code was changed to properly link records to invoices using the new invoice and invoice date columns. Also, the sources for various GL accounting codes were changed to match the default parameter sources from the regular daily job for stage_om_order1.
    4. Stage_om_order3 and stage_om_order were changed from SET to CURSOR SET so that any unknown dimension values are added to their respective dimension tables.
    5. If same records as historic load are coming into the fact, changes were made to fact_om_order_history, both regular and custom code, to ensure that daily loads work right. These changes also ensure the dss_current_flags were being set properly. This fix includes logic to pick up only the most recent version of an order on a daily load. For example, if a person has not run the daily load in some time and during that time they made three discernable changes to an order, only the most recent version of the order is added to the fact table. If the changes happened over a number of days and the daily load ran each day, each change would be reflected in the fact. However, the fact should still reflect the correct values as of the current date after the daily has been run.
    6. We are now letting invoice transaction_type records of ISS-COR, correcting invoices.
    7. If the data comes from an invoice record, stage_om_order_header_all now includes invoice_number (see #2) and line_number. If it comes from a sales_order, then it sets a special "no invoice" value for the invoice number and sets the line number to 0. We include this information at the header level because without it, we could potentially link an invoice line from one invoice to the invoice header from another invoice, especially if there were multiple invoices in one day. Previously, we were ranking invoice headers based on the date and time they were generated, thus causing us to orphan invoice lines that were not part of the last invoice of a given day. Because of this, the grain of the table changed. If there has not been an invoice for a given line, it does not match on line and invoice. It just matches on the header info from the sales order, presuming that exists.
    8. The changes we made in #7 to the logic that allows us to match on invoice in stage_om_order1, also forced changes to the joining logic for stage_om_shipment1, which uses the invoice number and line number (if it exists), and stage_om_booking1, which joins on item_number to fix the grain match. This also included adding invoice_number to the stage_om_ship_transactions table to be fed up from the perm tables. Booking orders only use transaction record (tr_hist) records, which have ORD-SO transaction types, so we can never match on the invoice as it does not exist in those records.
    Metadata
    (Purchasing)
    QBI-2417
    Parameter PO_SNAPSHOT_HIST_START_DATE is now used during the History load processing. These parameters should be set before running HIST_START for any source.
     
    QBI-2442
    fact_po_order_history update for both Historical and Daily loads now uses the parameter PO_ORDER_HISTORY_DATE for assignment of values to the dss_history_start_date and dss_history_end_date columns. Prior BI Versions used the current date (GETDATE).