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 Lineitemauto_update_time_returned_bill: bigintauto_update_time_returned_lineitem: bigintbill_amount: doublePKbill_id: bigintbill_number: stringdim_bill_outlier_status_id: bigintdim_event_date_id: bigintdim_event_time_id: bigintdim_event_user_id: bigintdim_event_zone_till_id: bigintdim_item_id: bigintdim_latest_updated_date_id: bigintdim_latest_updated_time_id: bigintdim_parent_bill_outlier_status_id: bigintdim_parent_line_item_outlier_status_id: bigintdim_returned_type_id: bigintexchange_bill_id: bigintissued_bill_id: bigintissued_lineitem_id: bigintitem_code: stringline_item_amount: doubleline_item_discount: doublePKline_item_id: bigintline_item_value: doublenotes: stringparent_bill_number: stringquantity: doublerate: doubleyear: intPKdim_membership_id: bigintReturn TypeLKid: biginttype: stringOutlier_statusLKid: bigintstatus: stringOutlier StatusdateLKdate_id: intday_of_month: intweek_of_year: intmonth: stringyear: intquarter: stringweek_number: intweek_start_date: stringweek_end_date: stringday_of_week: stringmonth_no: intmonth_no_of_year: intmonth_of_year: stringday_of_week_no: intquarter_no: intyearly_quarter_no: intdate: stringDateTimeLKtime_id: biginttime: stringhour_of_day: intminute_of_day: intday_shift: stringday_shift_no: inthour_range: stringTimeusersauto_update_merged_customer: bigintauto_update_loyalty: bigintfraud_status: stringtest_control_bucket: stringis_merged_customer: stringsubscription_status_email_bulk: stringfirst_name: stringslab_name: stringsubscription_status_wechat_bulk: stringemail: stringlast_name: stringmerged_user_id: bigintLKuser_id: bigintsubscription_status_wechat_trans: stringsubscription_status_sms_bulk: stringndnc_status: stringsubscription_status_email_trans: stringtest_control_status: stringauto_update_fraud_user: bigintis_inactive: stringsource: stringslab_number: intauto_update_users: bigintregistered_till_id: bigintauto_update_customer_enrollment: bigintslab_expiry_date: stringcustomer_external_id: stringsubscription_status_sms_trans: stringmobile: stringloyalty_type: stringauto_update_ndnc_status: bigintUsersZone_tillsstore_country: stringexternal_id_1: stringstore: stringauto_update_till_parent: bigintarea: stringstore_name: stringstore_city: stringstore_state: stringtill: stringauto_update_till_store_relation: bigintis_ffc_enabled: stringLKtill_id: biginttype: stringtill_description: stringauto_update_till: bigintstore_channel: stringexternal_id: stringis_billable: stringstore_id: bigintis_active: stringauto_update_store: bigintstore_description: stringlatitude: stringtimezone: stringexternal_id_2: stringtill_name: stringzone_name: stringZone TillsItemitem_code: stringbrand_name: stringstyle: stringsize: stringinventory_description: stringimage_url: stringcolor: stringis_valid: stringLKitem_id: bigintauto_update_inv_masters: bigintprice: stringItemMembership_card_presentLKid: biginttype: string
Membership Card Present
Membership TypeReturn TypeReturn Bill Lineitem

Legend

PK   Primary Key

LK   Linking Key

Fact Table
Dimension Table

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