search

ivylabs / suitecrm-data-integration

6 stars 5 forks source link

Service Line Items appear as 'Not Set' in 'Agent Sales Report' #140

Closed fraserward closed 3 years ago

fraserward commented 3 years ago

Services Line Items in CRM generate a Product_ID of '0' and as such are 'Not Set' in the report.

Discussion needed on how to deal with Services as the come through in the invoice, product_quote and line_item_group tables. They don't have set items and categories like products. Their name can be anything with a value placed on the service.

Adding them as a separate row line at the bottom of the report or removing them from the Sale report will mean looking for Product_ID's of zero's and cross referencing them with the other tables mention above.

Also Service Line items bring 'null' values into the Invoice Management Fact table (LINE_QUANTITY, LINE_COST_PRICE_AMOUNT, LINE_TOTAL_DISCOUNT_AMOUNT). This should be addressed.

Discuss.

harrisward commented 3 years ago

Discussed 19th May:

Adhoc Service Line items need to be visible on a report. We could possibly associate a service line fact transaction with the PRODUCT_TK of -1

harrisward commented 3 years ago

Modify F_SALES and F_INVOICE_MANAGEMENT transformation to assign a -1 PRODUCT_TK for all products with a PRODUCT_ID of 0.

Modify Priming SQL Below

Priming Dimension Specs:

PRODUCT_TK: -1 PRODUCT_ID: N/A PRODUCT_NAME: Service Line Item PRODUCT_DESCRIPTION: Service Line Item PRODUCT_CATEGORY_NAME: Service Line Item PRODUCT_CATEGORY_DESCRIPTION: Service Line Item

Replace NULLS on fact table for the following fields:

SALE_QUANTITY SALE_COST_PRICE_AMOUNT SALE_TOTAL_DISCOUNT_AMOUNT

Set to 0

fraserward commented 3 years ago

Updated Invoice Management & Sale Fact transformations and Products Dimension DDL SQL.

Pull Request: https://github.com/ivylabs/suitecrm-data-integration/pull/171