Return Bill Line Item Fact Table
The return bill line item fact table captures the returned transaction events at the line item level. It captures data such as return date and time, bill amount, unique identifier for bill and line item, item code, quantity, discount, and rate. This table is linked to multiple dimension tables, including date, time, users, zone till, item, outlier status, return type, and membership type, allowing for detailed analysis and reporting of return transactions.
Databricks Table Name: return_bill_lineitem
Return Bill Line item - Entity Relationship Diagram (ERD)
Zoom in the table for more clarity. Click the table title to view its details.
Return Bill Line item Fact Table
| Column Name | Data Type | Description | Linked Table | Availability for Export in the Return Transactions Template |
|---|---|---|---|---|
| auto_update_time _returned_bill | bigint | Date and time when the corresponding record in the returned bill table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto Update Time Returned Bill |
| auto_update_time _returned_lineitem | bigint | Date and time when the corresponding record in the returned lineitem table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto Update Time Returned Lineitem |
| bill_amount | double | The net bill amount after returning a purchase: Bill amount - the cost of return item | _ | Yes; Measure Name: Bill Amount |
| bill_id | bigint | Unique identifier assigned to a bill internally by Capillary system (bill id for return). It is the primary key for the table. | _ | Yes; Measure Name: Bill Id |
| bill_number | string | The unique number of a particular return bill which is either generated at the POS machine or provided manually. | _ | Yes; Measure Name: Bill Number |
| dim_bill_outlier_status_id | bigint | Captures whether a bill, identified by its bill_id, contains outlier items. | outlier_status | Yes; Dimension Name: Bill Outlier > Outlier Status |
| dim_event_date_id | bigint | Date when the return was done. Corresponds to the date_id column of the date table. | date | Yes; Dimension Name: Date > Date |
| dim_event_time_id | bigint | Timestamp of the return, capturing up to minutes (seconds are not captured). Corresponds to the time_id column of the time table. | time | Yes; Dimension Name: Time > Time Id |
| dim_event_user_id | bigint | Identifies the user associated with the event. It is an internally assigned value by Capillary for user identification. | users | Yes; Dimension Name: User > User Id |
| dim_event_zone_till_id | bigint | Identifier assigned to the point-of-sale (POS) terminal within a store, where the return is done. Corresponds to the till_id column of the zone_till dimension table. | zone till | Yes; Dimension Name: Store Hierarchy > Till Id |
| dim_item_id | bigint | Unique identifier generated by the system for the returned item. | item | Yes; Dimension Name: Product > Item Id |
| dim_latest_updated_date_id | bigint | Date when the return bill information is updated. Corresponds to the date_id column of the date table. | date | Yes; Dimension Name: Latest Updated Date > Date |
| dim_latest_updated_time_id | bigint | Time when the return bill level information is updated. Corresponds to the time_id column of the time table. | time | Yes; Dimension Name: Latest Updated Time > Time Id |
| dim_parent_bill _outlier_status_id | bigint | Status indicating if parent bill is an outlier. | outlier_status | Yes; Dimension Name: Parent Bill Outlier Status > Id |
| dim_parent_line _item_outlier_status_id | bigint | Status indicating if line item is an outlier. | outlier_status | Yes; Dimension Name: Parent Bill Lineitem Outlier Status > Id |
| dim_returned_type_id | bigint | Identifier for the type of return. Possible values are - Full, line item, amount, and cancelled. | return_type | Yes; Dimension Name: Returned Type > Id |
| exchange_bill_id | bigint | Unique identifier generated internally by Capillary for the mixed transaction (i.e, item exchange). | _ | Yes; Measure Name: Exchange Bill Id |
| issued_bill_id | bigint | Unique bill ID issued to the customer for that particular transaction. | _ | Yes; Measure Name: Issued Bill Id |
| issued_lineitem_id | bigint | Unique line-item ID issued to the customer for that particular line-item of a transaction. | _ | Yes; Measure Name: Issued Lineitem Id |
| item_code | string | Unique item code of the returned line item. | _ | Yes; Measure Name: Item Code |
| line_item_amount | double | Total amount for the returned line item, including discount and tax. | _ | Yes; Measure Name: Line Item Amount |
| line_item_discount | double | Discount given for the particular line-item. | _ | Yes; Measure Name: Line Item Discount |
| line_item_id | bigint | Unique identifier generated internally for the line-item. It is the primary key of the table. | _ | Yes; Measure Name: Line Item Id |
| line_item_value | double | Price of the line item excluding discount and tax. | _ | Yes; Measure Name: Line Item Value |
| notes | string | Any specific note mentioned for the return transaction. | _ | Yes; Measure Name: Notes |
| parent_bill_number | string | Actual transaction number of the return item used in case of exchange. It is blank if the parent bill number is not mentioned during return. | _ | Yes; Measure Name: Parent Bill Number |
| quantity | double | Quantity of the specific line-item returned. | _ | Yes; Measure Name: Quantity |
| rate | double | Price of the line item (item price * item quantity). | _ | Yes; Measure Name: Rate |
| year | int | Year when the return was done. | _ | Yes; Dimension Name: Year |
| dim_membership_id | bigint | Specifies the membership type of the customer who completed the transaction. | membership type | Yes; Dimension Name: Membership > Id |
Updated 9 months ago